How to Hide Add Record Operation?

[David Habercom]

I have a drop-down list based on a table (Table2) different than the main
form table (Table1). When I click an "Add" button, a hidden combo box
appears based on yet another table (Table3). On clicking in the combo box, I
then have the two pieces of data (from Tables 1 & 3) I need to add another
record to Table2. But I don't know how to do that without yanking my screen
around. DoCmd.OpenTable does the job, but Table2 flashes open and sits
there. What is the simplest way to do this behind the scenes, so the user
won't see anything until a requery shows the new record in the Table2
dropdown list?

Thanks in advance.

 

[Dirk Goldgar]

I have a drop-down list based on a table (Table2) different than the
main form table (Table1). When I click an "Add" button, a hidden
combo box appears based on yet another table (Table3). On clicking
in the combo box, I then have the two pieces of data (from Tables 1 &
3) I need to add another record to Table2. But I don't know how to
do that without yanking my screen around. DoCmd.OpenTable does the
job, but Table2 flashes open and sits there. What is the simplest
way to do this behind the scenes, so the user won't see anything
until a requery shows the new record in the Table2 dropdown list?

There's no reason to use OpenTable when all you want to do is add a
record with known values to a table. In fact, I'd argue that there is
never any reason to use OpenTable, period.

All you really want to do is execute an append query that incorporates
the data from the controls on the main form. You don't give the names
of the controls, nor the names and types of the fields in Table2, but
consider this example:

Dim strSQL As String

strSQL = _
"INSERT INTO Table2 (SomeField, SomeOtherField) " & _
"VALUES (" & _
Chr(34) & Me.txtSomeControl & Chr(34) & _
", " & _
Chr(34) & Me.cboSomeOtherControl & Chr(34) & _
")"

CurrentDb.Execute strSQL, dbFailOnError

That example assumes both of the fields (in Table2) are text fields. If
the fields are number fields (of whatever subtype), remove the "Chr(34)
& " and " & Chr(34)" from around the control references. If the fields
are dates, you have to do something else instead, but I won't bother
with that in this message -- just let me know and I'll get into it.

 

[David Habercom]

That ought to do it. Thanks, Dirk...again!