Where to put INSERT INTO

[lorirobn]

Hi,

I have a form that displays fields from a table (tblRoomHeader), using a Select Query as its record source. If field 'CurrentUse' is changed to a particular value, I need to insert a record onto another table, tblGuestRoom (only need to populate one field).

I have not programmed an Insert before on a form.

Can someone please advise the best place to place the insert logic?

And, is it best to have the SQL right in my module, or set up a Query to do this?

I have set up logic to look something like this.

dim strInsertSQL as string
strInsertSQL = "INSERT INTO tblGuestRoom ( RoomNumber ) " _
& " SELECT tblRoomHeader.RoomNumber FROM tblRoomHeader " _
& " WHERE (((tblRoomHeader.RoomNumber) = '" & RoomNumber & "'));"

Just not sure where to put it or what to do with it!

Thanks in advance for any help,
Lori

 

[KenReay]

Hi

You could put the code in the after update event of the control "CurrentUse"

For the sake of inserting one record I do not see any advantage in setting up a querydef, you could jsut execute the SQL using docmd.runsql

eg

docmd.setwarnings false
docmd.runsql strInsertSQL
docmd.setwarnings true

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lorirobn]

Hi Ken,

Thanks for your help - I got it working doing exactly what you said. Just out of curiosity, though, why do I need to setwarnings off before the Insert?

Thanks again,
Lori

 

[KenReay]

Hi

To suppress Access warning message "You are about to Insert one row ..etc"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lorirobn]

Oh! Thanks....Lori