update query in form view

[Guest]

I have been doing update queries using the docmd.openquery, but now I need to
do it inside the form. The user is going to select a field on a record, and
I need to use the forms filter and the record they chose to run an update
query. i.e.

update tblcircuit set toinfoid = & (NEW FIELD THEY SELECT FRM CMBOBOX) &
from tlbcircuit where toinfoid = & (OLDID FROM RECORD THEY SELECTED) & " and
" & me.filter & ""

Any ideas on how I can accomplish this. There will be several records in
the table where the toinfoid is eqal to the toinfoid of the record they
select, they will select a new toinfo id from a combobox (this gets list of
available id's from another table)
then I want to change all values of exist toinfoid to the new toinfoid using
the forms filter criteria.

I am stumped on how to go about this.

 

[Marshall Barton]

I have been doing update queries using the docmd.openquery, but now I need to
do it inside the form. The user is going to select a field on a record, and
I need to use the forms filter and the record they chose to run an update
query. i.e.

update tblcircuit set toinfoid = & (NEW FIELD THEY SELECT FRM CMBOBOX) &
from tlbcircuit where toinfoid = & (OLDID FROM RECORD THEY SELECTED) & " and
" & me.filter & ""

Any ideas on how I can accomplish this. There will be several records in
the table where the toinfoid is eqal to the toinfoid of the record they
select, they will select a new toinfo id from a combobox (this gets list of
available id's from another table)
then I want to change all values of exist toinfoid to the new toinfoid using
the forms filter criteria.

If the toinfoid field is a numeric type, then the code would
be something like:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "UPDATE tblcircuit SET toinfoid=" & Me.cmbobox _
& " WHERE toinfoid=" & Me.someothercontrol _
& " AND " & Me.Filter
db.Execute strSQL, dbFailOnError
Set db = Nothing

If toinfoid is a Text field, then the query needs quotes
around the values:
strSQL = "UPDATE tblcircuit SET toinfoid=""" & Me.cmbobox _
& """ WHERE toinfoid=""" & Me.someothercontrol _
& """ AND " & Me.Filter

If toinfoid is a Date/Time field, then the query need #
signs around the values:
strSQL = "UPDATE tblcircuit SET toinfoid=#" & Me.cmbobox _
& "# WHERE toinfoid=#" & Me.someothercontrol _
& "# AND " & Me.Filter
If there is any chance that a user might not be using US
date settings in windows, the values will need to be
formatted to a US format:
. . . & Format(Me.cmbobox, "m\/d\/yyyy") & . . .