SQL code help

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am trying to set up a command button on a form which
will open a new record while copying some of the field
values from the current record. We receive multiple types
of samples from our clients. Without having to type the
same client information in each record for each of the
sample types, I would like to copy all those fields that
stay the same (like project #, project location, etc.) and
fill in only the data that changes (Sample type, Sample
condition, etc.)

I there any way to modify the current code, for
duplicating the current record, to exclude some fields
from the duplication process? The current code written by
Access is as follows:

Private Sub AddMaterial_Click()
On Error GoTo Err_AddMaterial_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, ,
acMenuVer70 'Paste Append

Exit_AddMaterial_Click:
Exit Sub

Err_AddMaterial_Click:
MsgBox Err.Description
Resume Exit_AddMaterial_Click

End Sub

Thank you for your help in advance.

John
 
John said:
I am trying to set up a command button on a form which
will open a new record while copying some of the field
values from the current record. We receive multiple types
of samples from our clients. Without having to type the
same client information in each record for each of the
sample types, I would like to copy all those fields that
stay the same (like project #, project location, etc.) and
fill in only the data that changes (Sample type, Sample
condition, etc.)

I there any way to modify the current code, for
duplicating the current record, to exclude some fields
from the duplication process? The current code written by
Access is as follows:

Private Sub AddMaterial_Click()
On Error GoTo Err_AddMaterial_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, ,
acMenuVer70 'Paste Append

Exit_AddMaterial_Click:
Exit Sub

Err_AddMaterial_Click:
MsgBox Err.Description
Resume Exit_AddMaterial_Click

End Sub

Thank you for your help in advance.

John
Click to expand...

(Note that this question doesn't actually have anything to do with SQL.
Instead, you're dealing with VBA code and the Access object model.)

The need to copy multiple client- and project-descriptive fields for
each record suggests that your table may not be in its ideal structure.
Normally you would store only the primary key(s) of a Projects table in
this "child" table, and use queries or run-time lookups to connect the
ProjectMaterials table to the Projects table and get the related
information about the project.

That said, if you want to modify that code to create a new record from
only certain fields of the current record, you're going to have to
capture each of those field values in a separate variable, then go to
the new record, and then assign the field values back from the variables
to the fields. It might look something like this:

'------ start of example code ------
Private Sub AddMaterial_Click()

On Error GoTo Err_AddMaterial_Click

Dim varField1 As Variant
Dim varField2 As Variant
Dim varField3 As Variant
Dim varField4 As Variant

' Save field values from current record.
varField1 = Me.Field1
varField2 = Me.Field2
varField3 = Me.Field3
varField4 = Me.Field4

' Go to a new record.
RunCommand acCmdRecordsGoToNew

' Set new record fields from saved values.
Me.Field1 = varField1
Me.Field2 = varField2
Me.Field3 = varField3
Me.Field4 = varField4

Exit_AddMaterial_Click:
Exit Sub

Err_AddMaterial_Click:
MsgBox Err.Description
Resume Exit_AddMaterial_Click

End Sub
'------ end of example code ------
 
Back
Top