Moving a record.

[Cutter596]

This is sample db i am working with to test button: the real db will have many more fields.

Table1 = "Asperants" with fields AsperantsID, FirstName and LastName
Table2 = "Clergy" with fields ClergyID, FirstName and LastName

My code is from a form called frmAsperants using a button called comMove

The problem I am having is that i get prompted for the Me.txtAsperantsID number when i run the query. If i manually type in the number the append works. what am i missing to make the query us the current AsperantsID in the form?

Private Sub comMove_Click()
On Error GoTo Err_comMove_Click


Dim strSQL As String
strSQL = "INSERT INTO Clergy(FirstName,LastName)"
strSQL = strSQL + "SELECT Asperants.FirstName, Asperants.LastName "
strSQL = strSQL + "FROM Asperants "
strSQL = strSQL + "WHERE (((Asperants.AsperantsID)=Me.txtAsperantsID));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


Exit_comMove_Click:
Exit Sub

Err_comMove_Click:
MsgBox Err.Description
Resume Exit_comMove_Click

End Sub

[TheShabz]

strSQL = strSQL + "WHERE (((Asperants.AsperantsID)=" & Me.txtAsperantsID & "));"

Me.txtAsperantsID is not a query reference, but a VBA one. You have to remove it from the string so that it's value can be referenced concatenated into the string to be queried. Otherwise, you're telling access to look for table Me and field txtAsperantsID. Do a Google search for "vba string building".

On a side note, I'd restructure your tables. You only need 1 table if you add in a PersonType field where you can enter in "Clergy" or "Asperant". To fully normalize it, you would create a second table called PeopleTypes with all the different types you would use then put the ID field in your main table as a foreign key.