How to write result to two tables from combo box

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

I have a combo box on a form and when a selection is made I want to
write it to the underlying table (this is happening now), but I also
want to write it to a separate table. I believe that i probably have to
do it in the After Update section but have no idea how. Any help would
be appreciated.

regards Wayne
 
Sorry Guys i didnt explain myself properly,

I have a Form (connections_form) , 2 tables (ADSLIP_tbl and
Connection_tbl)

On the connection_form I have a combo box that allows selection from
ADSLIP_tbl of an IPnumber (field_ipnum) that has an empty login
(field_iploginname) field in the same record .In other words Im
selecting unassigned Ip numbers.

Above the combo box on connection_form and filled in first is a textbox
that contains the login name (txt_loginname))

Once the ipnumber is selected it is written to the underlying table
(connection_tbl) into the existing record automatically.

What i also want to do automatically is to write the txt_loginname
from the form into the exisitng field_iplogin in the table adslip_tbl.
Obviously this is the same record that the field_ipnum is in.

Hopefully this explains it better, thanks.
 
You'll need to change the insert to an update and add a WHERE clause to make
sure you're on the same record:

CurrentDb.Execute "UPDATE Table2 SET Table2.SomeField ="'" & Me.txtLoginName
& "'" WHERE SomeIDField = & Me.txtID;"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin thats great thanks, I understand how it works but i cant get the
syntax right ( Im still learning my way around access and am finding it
a bit different to what im used to. This is the actual code and it
works apart from I cant seem to attach the WHERE clause to it.
CurrentDb.Execute "UPDATE adslip_tbl SET adslip_tbl.adsllogin = '" &
Me.Field1 & "'"

REM & WHERE field_adslip = & Me.Combo121;"

without the where clause it happily replaces the field in every record.
heh :). I seem to get all the " and ' in the wrong place.

thanks.
 
Try this (watch for line wrapping):

Dim strSQL As String

strSQL = "UPDATE adslip_tbl SET adslip_tbl.adsllogin = '" & Me.Field1 & "'"
& "WHERE [adslip_tbl].[field_adslip] = " & Me.Combo121

Debug.Print strSQL

' CurrentDB.Execute strSQL

Notice how I commented out the execute line? Run this and copy the
resulting values from the Immediate Window. Then paste the resulting SQL
string into a query SQL window and click on the grid window button (all the
way on the left). Keep fiddling until it works. Then uncomment the execute
line and your home.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin, Thankyou very much, that works great and you have prompted some
memories for me as well, i appreciate your help.
Wayne
 
Back
Top