image in the right is the criteria on when the process of copying will trigger. First, based from the value of the "Select Here" which is the "CODE" in the fields what ever the code matches on the table will be copied. Two, the checkbox is a trigger also to what table will be the source and destination table will the data will insert into. By the way all tables are link to sql server. can anyone help what codes will be used here?
Noted: Source tablename is definite with the "@" sign of the name.
used codes:
CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';" - doesn't work
Run-time error '3622' - You must use the dbseechanges opetion with openrecordset when accessing a sql server table that has an identity column.
CurrentDb.Execute "INSERT INTO [Temp_promo] SELECT fields1,fields1,fields3 FROM [Promo] WHERE [code] = '" & Me.cmbcode & "';" - This work but i need to changed the "@PROMO" table to "PROMO" table, which as i said must definite to "@PROMO" only.
Private Sub Copy_Click()
Dim db As Database
Dim rsSource As Recordset
Dim rsDestination As Recordset
If Shw1 = True Then
'Open a recordset for the source table'
Set db = CurrentDb
Set rsSource = db.OpenRecordset("SELECT Code,Name FROM [@Promo] WHERE [code] = '" & Me.cmbcode & "';", dbOpenDynaset, dbSeeChanges)
'Open a recordset for the destination table'
Set rsDestination = db.OpenRecordset("TEMP_PROMO", dbOpenDynaset, dbSeeChanges)
'Check if there are records in the source Recordset '
If Not rsSource.EOF Then
rsSource.MoveFirst 'Move to the first record in the source Recordset'
'Loop through records in the source Recordset'
Do Until rsSource.EOF
'Add a new record to the destination Recordset'
rsDestination.AddNew
'Set values in the destination Recordset based on the source Recordset'
rsDestination!Code = rsSource!Code
rsDestination!Name = rsSource!Name
'Repeat for other fields...'
'Update the destination Recordset'
rsDestination.Update
'Move to the next record in the source Recordset'
rsSource.MoveNext
Loop
End If
rsSource.Close
rsDestination.Close
Set rsSource = Nothing
Set rsDestination = Nothing
Else
'Do nothing'
End If
- this work but for only one table only to another only. adding more will not work.
annyone can help me about this? very much appriciated