Newbie: Getting combobox to show new data in list?

[Ed from AZ]

(Access 2007)

I have a table of vehicle types, a query based on that table, and a
combobox based on the query. I would like the user to be able to
enter something not in the current list of the combobox, have that
entered into the table, and then have the new entry appear in the
combobox list.

I found the following code in a book to enable adding the entery to
the table:

Private Sub lstVehType_NotInList(NewData As String, Response As
Integer)

If MsgBox("This vehicle type is not in the current list." & vbCrLf &
_
"Do you want to add " & NewData & " to the list of vehicle
types?", _
vbYesNo, "Add New Vehicle Type?") = vbYes Then
DoCmd.RunSQL "INSERT INTO tblVehOnly(VehicleType) VALUES ('" &
_
NewData & "')"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The record source for my combobox is:
SELECT tblVehSN.VehID, tblVehSN.VehicleSN

I'm thinking that merely adding the new item to the table is not going
to change the data in the combo because the query wasn't refreshed -
yes? So after adding the new data, I need to re-reun the query and
then refresh the combo - yes? I found the .Refresh method for
controls, so I think I can handle that.

But I'm not quite sure how to handle re-running the query. I looked
up Requery in Help, and it pointed me away from the macro and DoCmd
Requery methods to the VBA method. That looks like it needs ADO and a
Recordset object??

Am I once again doing things the hard way? What objects/methods
should I be looking at?

Ed

 

[Arvin Meyer [MVP]]

Am I once again doing things the hard way? What objects/methods
should I be looking at?

Yes. A simple:

Me.ComboboxName.Requery

is sufficient. Using acErrAdded does the same thing, so instead of:

acErrContinue, use:

Response = acDataErrAdded

 

[Ed from AZ]

Thank you, Arvin!

Yes.

I hope to really get this some day . . . . 8>/

One more question: if I open the form and then try to change to
Design View, or if I try to close the form with the X, and if nothing
has been entered, I get an error that "Index or primary key can't
contain a Null value."

Can I get around this? I'd like the user (and me!!) to be able to
close the form without seeing an error. I do have a button to close
the form, coded with:
DoCmd.Close acForm, "frmAddVeh", acSaveNo
If I use that with no data entered, it closes with no error.

Ed

 

[Arvin Meyer [MVP]]

You may have a combo box based upon a lookup field in a table:

http://www.mvps.org/access/lookupfields.htm

You may have then filtered the form and it thinks you have entered data. Try
hitting the Esc key twice. You shouldn't get an error after doing so. The
Esc key returns the form to its pristine state by undoing any changes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thank you, Arvin!

Yes.

I hope to really get this some day . . . . 8>/

One more question: if I open the form and then try to change to
Design View, or if I try to close the form with the X, and if nothing
has been entered, I get an error that "Index or primary key can't
contain a Null value."

Can I get around this? I'd like the user (and me!!) to be able to
close the form without seeing an error. I do have a button to close
the form, coded with:
DoCmd.Close acForm, "frmAddVeh", acSaveNo
If I use that with no data entered, it closes with no error.

Ed

 

[Ed from AZ]

Thanks so much! I think I just might be almost done with this
thing!! <BG>

Ed