Using combo box to add new records and search for existing records

[Jason Lim]

Dear all,

I am really confused now. Please help.

errrm please do allow me to explain everything from the very
beginning.

I am trying to have combo box which allows me to ADD new records and
SEARCH for existing records.

I actually have a main form and a subform.

The main form is the combox box, and in its AfterUpdate property, I
set the function SetFilter(). The OnOpen event of the main form is
also set with SetFilter(). The function is:
----------------------------------------------------------------------
Sub SetFilter()

Dim LSQL As String

LSQL = "select * from Fabrication"
LSQL = LSQL & " where ProductID = '" & cboSelected & "'"

Form_Fabrication_sub1.RecordSource = LSQL

End Sub
-----------------------------------------------------------------------

Fabrication_sub1 is my subform. The record source of subform is:
SELECT * FROM Fabrication WHERE ProductID='';

Annd I have a table named Fabrication, together with fields such as
ProductID, Purpose, etc.

Now my form works fine in SEARCHING for EXISTING records (I type them
manually in the table).
But, I cant't add new records based on selected ProductID from the
combox box.

For example, I select ProductID from the combo box, XY123.
Fine, the subforms displays the existing records (date, quantity,
purpose, etc).

But, when I select XY128 and type in the records (date, quantity,
purpose), XY128 is not saved in the table. Date, quantity, purpose are
saved.
So, how to save the ProductID selection into the table after the
combox box is clicked?

Thanks a lot in advance.
=)))

 

[Arvin Meyer [MVP]]

Use the NotInList event to add new records. There should be examples in the
help files, and I have a generic public function at my website:

http://www.datastrat.com/Code/NotInListCode.txt

and a demo at the Access MVP website:

http://www.accessmvp.com/Arvin/NotInListDemo.zip

 

[pietlinden]

why not base the subform on the fabrication table (or just use the
query) and enforce referential integrity? Then just have a combobox
for filtering records?