DoCopy access Table -- Duplicating Records

[melgra2017]

Hi, I am still very new to VBA especially in Access. What I am trying to do is use a form with buttons (with code) that copies a table and pastes it with today's date. So that the user doesn't overwrite the data each time they import a new file. It works however it is duplicating the records in the table and can't seem to figure out why.

The form has 3 buttons
- Step 1 Copy & Paste
- Step 2 Import
- Step 3 Rename & Exports the file to a network folder

Step 1 -- code is as follows
[Private Sub Command2_Click()

DoCmd.CopyObject , "ShipStatusFm_ExportList_previous_" & Format(Date, "mmddyy"), acTable, "ShipStatusFm_ExportList"
]

Any help is greatly appreciated.




[/CODE]

[OBP]

I am not sure I completely understand what you are actually doing.
However to prevent duplication there should be some kind of "flag" or indicator in the table to prevent duplicated effort.
There should also be a Master Key field which also prevents record duplication.

[melgra2017]

All I am trying to do is copy the source data table before the new data is imported an overwrites the table.

[OBP]

Is the new data an "updated" version of the original data, or completely new data?

[melgra2017]

It will be new updated each month of the original. Try to make sure I can go back if the user has issues.

[OBP]

If the table is not too large I would suggest that you have an Archive table, this can be updated prior to each import by an Append Query that adds the current data to the Archive table and then runs a Delete query that removes the records in the current table.
The Archive table would have an extra field for the Date of transfer so that you can call up the records for a particular month.
The Archive table could even be in a separate Archive database if required.

[lmeser61]

I have a rather complex problem I am working on and would appreciate any help I can get. I work at an electronics manufacturing place and we create electronic boards and assemblies. One of the tasks assigned are to do crimps. I have to make a form that, when a person enters the crimp height measurement it checks that value against the standard crimp Lo/Hi for that measurement. If it meets the standard, then the person can continue with finishing the assembly, otherwise it should stop them and not allow them to go any further and to alert them to seek a supervisor. The problem I am encountering is that there may be multiple crimp numbers associated with the different gauges.

Here is an example: For Crimp # PA74-0000-96 there are two crimp assemblies that utilize 12AWG (which the standard is 0.096" + 0.002). None use the crimp #208-060, 12AWG (where the standard is also 0.096+ 0.002) and 108 different assemblies use Crimp # 208-032, 12AWG (standard 0.071"+ 0.002).

Here is what I have written for code so far but I can't seem to get it to run.


Function InTolerance(Gauge, CrimpHeight)
Dim CrimpHeight As Double 'Declare variable as data type
Dim Gauge As String
Dim CrimpNum As String
Dim Prompt, CrimpNum, Gauge



Gauge = "AWG Standard(CrimpHeightLo,CrimpHeightHi)"

Prompt = "Crimp #:"
Prompt = "Gauge Used:"

CrimpNum = InputBox$(Prompt)
Label1.Caption = CrimpNum
Label2.Caption = Gauge

Select Case Gauge ' Ensure correct Gauge is matched with crimp# to get correct Lo and Hi standard for that crimp#/AWG
where CrimpNum = CrimpNum(Gauge)
Eval (IIf(CrimpHeight >= CrimpHeightLo And CrimpHeight <= CrimpHeightHi, CrimpNum = CrimpNum(Gauge) Or CrimpNum(Gauge) = "N/A"))
Else
CrimpNum = CrimpNum
End Select


If CrimpHeight >= CrimpHeightLo And CrimpHeight <= CrimpHeightHi Then 'Evaluate whether or not CrimpHeight number is between Lo/Hi values; color green
MsgBox ("Ok, you are good to proceed.")
TxtColor Green
Else
If CrimpHeight <= CrimpHeightLo And CrimpHeight >= CrimpHeightHi Then 'Evaluate whether or not CrimpHeight number is outside Lo/Hi values; color Red
MsgBox ("STOP! Do Not Proceed! Please see Supervisor.")
TxtColor Red

End Function


In the form itself I put: SELECT CrimpNum, Gauge, CrimpHeight, fnInTolerance(Gauge, CrimpHeight) as Tolerance
FROM tblCrimps

[melgra2017]

That was my next step. However, I was trying to use VBA to get more familiar with it. Thanks for the help.

[OBP]

Hello, instead of Prompts I would probably use Combo boxes to select required Crimp # and Gauge Used.
What is not working in your code.
ps Your code does not have any Error trapping, should one occur.

[OBP]

OK, Learning VBA is good, but where & when to apply it is also essential for smooth operation.
Do you need further assistance?

[melgra2017]

No, I’m good.

[lmeser61]

On the form, crimp # and gauge are combo boxes. It's when a person enters a crimp height that I have to validate against what they entered to what the standard is to tell them whether or not they need to stop and get a supervisor.

[OBP]

lmeser61, I notice that you are using a function for your code.
I would use the Combo's "After Update" Event Procedure for the code .