Query and data entry in a combo box

[williekay]

Can you have a Table with a field called DATE, that is Indexed with NO DUPLICATES, can you then create a form with a combo box for the date that will do one of two things. If you enter a date that is in the table, the record appears, without the ability to edit, if you enter a date that is not in the table you can edit and enter a new record??

Willie

 

[scriverb]

Yes, this certainly can be done. But, first thing it is not recommended to have a field called Date. You see Date is a reserved word that names a Function: Date() which returns the Current Date. Not a good idea as ACCESS can get confused when referring to this field name. So, change the name of the field to Date_Of_Whatever. Now there won't be a problem.

You can set in your table the Date_Of_Whatever as the Primary Index and this will keep you from ever having duplicates. You can then use the Combobox Wizard to walk through the process of selecting a Date_Of_Whatever or entering a new one that is not in the list to either find your record or create a new record with that as the Primary Index. You should make sure that the allow entries not in list property is set to Yes.

Post back if you have any questions as this should get you started.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jyotko]

Willie,

You can do this by setting the form properties:

AllowEdits = False
AllowAdditions = True

You may have to add an event property to the control such as below or have the user click the new record button on the navigation bar on the bottom of the form.

Private Sub cboDate_DblClick(Cancel As Integer)
DoCmd.GoToRecord acDataForm, "frmTable1", acNewRec
End Sub