Add new record from Form Combo Box(?)

[zaphod2003]

Hi Guys

I know this should be easy (or maybe not), or maybe I'm having a bad day but I cannot figure out how to do this.

I have a table which contains a list of Shipment Numbers. I am creating a form which pulls in a list of these shipment numbers and allows a user to select a shipment to modify the current list of items on the shipment or add one of the shipment number does not yet exist.

This is where I am struggling a bit.

1, The table Shipment Numbers are not unique but I only want to display one instance of the shipment Number, not multiple instances of the shipment number.

2, If the shipment number is not in this list of current shipment numbers I want to assume a new record with that shipment number, not to amend exisiting record.

I've tried all sorts of controls but cannot figure out which one to use, although a combo seems the obvious choice. But how would I ensure that if a user entered a new record into the list it created a new record and not simply amend an existing one.

Any suggestions welcome.

 

[ajetrumpet]

1, The table Shipment Numbers are not unique but I only want to display one instance of the shipment Number, not multiple instances of the shipment number.

Use SELECT DISTINCT for this. It eliminates the problem.

2, If the shipment number is not in this list of current shipment numbers I want to assume a new record with that shipment number, not to amend exisiting record.

Then you will have to give the user some sort of direction for entering a new record (button, just enter name in list, etc...)

a combo seems the obvious choice. But how would I ensure that if a user entered a new record into the list it created a new record and not simply amend an existing one.

AMEND an existing one? Don't know what you mean by this. A combo box needs a rowsource anyway, and that right there tells you that it pulls records. Thus, each shipment name in the list corresponds to a record in a table, weather there be only one field in that record or not.

Other Helpful Info

** To add a value that is manually entered in a combo box, you have to .Requery it to show an updated list.

** A combo can be requeried to show an updated list only if it is bound to a field, because the manually entered value has to be stored somehwere so it can be queried along with the rest.

** To prevent a user from manually entering a shipment number and creating a new record when the number is already in the list, use DCount on an event to provide a warning message...

OnEvent

  If DCount("[field]", "table", "[field] = Forms!FormName!Control") > 0 Then
    MsgBox "We don't need another record of this shipment number.  Use the list!"
  End If

 

[zaphod2003]

Thanks

Thanks Adam

I most concise answer. I can see from your reponse I was on the right lines and have just implemented your suggestions and it works.

I forgot to add a new button (doh!) and didn't realise I needed to requery the combo.

Forest and trees I guess...

Thanks a lot. I was pulling out what little hair I have left.

 

[ajetrumpet]

Thanks Adam

Welcome.

I was pulling out what little hair I have left.

At least you saved some...