What is the normal way to handle NotInList

  • Thread starter Thread starter Frederick Wilson
  • Start date Start date
F

Frederick Wilson

Hello all,

I am just curious. I handle my notinlist with a recordset.

I will trap the not in list in the event for that control,
MsgBox to find out if they want it to be added

Yes

I open a recordset
addnew
update
close
responce=acdataerradded

then return them back to the control.

It occured to me last week that there just might be a way to do this via
a query but I am not sure how to make it work.

The reason? The PK for the record where the cboBox gets its info

with the recordset I simply do a dmax ("","") + 1 right before update.

What are your thoughts?
Fred
 
Running an action query using the Execute method of CurrentDb sometimes is a
bit quicker, but if you're just adding a single record, I'm not sure it's
worth changing what you're already using.

But, for information, you'd build an SQL statement that will be an update
query. Concatenate all the values into the SQL statement. Then run it by

CurrentDb.Execute "SQL Statement", dbFailOnError
 
Thanks Ken, But could you elaborate on what the query would look like?
Is this an update query that I would make and save? Or do I build it at
runtime such as in the notinlist event?

Thanks,
Fred
 
Having no idea of the fields in your table that need to get values, I will
give you a generic append query SQL statement. You can look in Help files
for more detailed info:

Dim strSQL As String
strSQL = "INSERT INTO Tablename ( Field1, Field2, Field3 ) " & _
"SELECT " & Me.ControlName1.Value & ", " & Me.ControlName2.Value & _
", " & Me.ControlName3.Value & ";"

Note that, if any of the values being inserted are text strings, you need to
delimit the value with ' characters; and if any are date strings, you need
to delimit the value with # characters.

One way to get a good picture of what the append query's SQL needs to look
like when you're done is to buiild the append query in the QBE window of the
queries; then go to SQL view and copy the statement.
 
Ken,

You're a good man.

I have used the query builder to do just as you said, but was not sure
how to get it into my string variable. I suspect that instead of one of
the values being Me.Control#.value, I would submit the DMAX statement I
eluded to at first.

I will play with this.

Thanks,
Fred
 
Back
Top