I have a form whose control source is a table. There is one unbound combobox on the form, and I want to use that as the "driver" for whether or not the form edits data already in the table or adds new data to the table. If the selection in the combo box results in a record already contained in the table, I want the form fields to update with all that data (for editing). If the selection in the combo box results in no records, I want to be able to type new data into the form fields and add a record to the table.

I keep thinking that I should make the control source a query that is based on the combo box selection, but I cannot get this to work right. Any ideas? Thanks for your help.

I've done something like that before where I pull the data from the table and added "<New Record>" on top of the selection, so that when the user selects <New Record>, then the form switches to a data entry form, but when they select any of the other data, the form pulls that record into the form for editing. Is that what you have in mind?

' If the selection in the combo box results in a record already contained in the table'

So the combo is not based on the data in the table then?

In the afterupdate event procedure for the combo put the equivalent of the next line, if the matchfield is a number:

Me.recordsource = "Select * from tablename where somefield -= " & me.comboname
Use your own table field and control names.

If there is a record it will be displayed; if there is not you will be on a new record.

If the matchfield is text use the next line instead:

Me.recordsource = "Select * from tablename where somefield -= '" & me.comboname &"'"

The row source for the combo box is not based on the data in the table, and the matchfield is text. I added the line:
Me.RecordSource = "Select * from Audits where AuditID = '" & Me.Audit & "'"

in the afterupdate event procedure, but this is what happens:

When I open the form, it shows the data from the first record in the control source table. Once I select somethign else in the combo box, it just updates that field for the current record, rather than moving to a new record. For example, let's say this is all of the data in the table:

Col1----------Col2--------Col3
AAA----------111---------a1a1

The Combo box has a list of values for Col1 - it has a larger selection than what is in the table. When I open the form, the combobox displays "AAA", and the 2 other text boxes will display 111 and a1a1. When I select something else from the combo box (lets say "BBB"), it will replace AAA with BBB for the current record. I want it to go to a new record and have the text boxes blank, since there is no existing BBB record in the table.

I think Peter wanted you to create an unbound combobox for this purpose.

Ok, I get it now - I think I got it to work. Thanks for your help!

You're welcome. Thanks to Peter for providing a quick solution. Good luck with your project.