Write value from a cell into multiple records

[pbaldy]

This has some good info:

http://www.baldyweb.com/BuildSQL.htm

 

[Lol999]

Thanks for that I'll have a look at the link.
I've just run the code but nothing is happening, the table I want to insert into is not being updated. Initially I had the wrong table to insert into but having changed it there's no action.

Here is what I have:
Private Sub Command7_Click()
Dim strSQL As String
strSQL = "INSERT INTO Tbl_Tracking (ID, Location) " _
& "SELECT ID, '" & Forms!Frm_Booking.Txt_Location & "' " _
& "FROM Tbl_Display"
CurrentDb.Execute strSQL
End Sub

Thoughts?

 

[pbaldy]

Are there records in the source table? Add this to get an error message (without it will fail silently):

CurrentDb.Execute strSQL, dbFailOnError

If ID is an autonumber, you wouldn't want to include it.

 

[isladogs]

Is the ID field in the destination table autonumber type?
If so, remove it from the SQL statement

 

[Lol999]

The ID field is the Primary key in two tables but is not an Autonumber.

I'm getting runtime error 3201, a related record is required in the Location table.

Turns out I was not entering the location code properly. D'Oh!

Many thanks for your help so far. What I need to do now is add code to delete the records from the Display table ready for the next batch of scanning and reset the value of the location textbox to "" ready for the next input.

I'll have a bash and see where I get to.

Many, many thanks for your help so far.

 

[pbaldy]

No problem, post back if you get stuck. You just execute a simple delete query.