Issues when an item is not already in combo box list

[zakster]

Hi everybody,

I am currently working on a quote form that has a combo box containing a list of clients. The client is a required field for the quote.

For clients that already exist in the database the user simply selects the client from the list in the combo box either by typing the client name or selecting it from the drop down list.

For clients that don't already exist in the database the user clicks on the 'Add' button to add a new client to the database.

All sounds logical I hope.

Now so far if the user clicks on the 'Add' button first up they can add a client and then select it to use for the quote - no problems. If the user selects a client from the combo box, and then changes their mind and uses the 'Add' button, again no problems.

The issue is when the user types in a client name into the combo box - and it is not found. At this stage if they leave the partially typed client name in the combo box and press the 'Add' button - they will get the "Item doesn't exist in the list error". If they delete what they have typed in the combo box and press the 'Add' button - they will get the "Client is a required field error".

In both of these scenarios the user can NOT continue on to add a new client - unless they first select a valid client from the combo list. And so I ask how can avoid this issue and allow these errors to be ignored so that the user can add a client more easily??

Also, as a side question how do I set the client on the quote form to be the client that has just been newly created through the client pop up screen?


Thanks for reading and hopefully helping

 

[Zaeed]

I would use an unbound comboBox, with the RowSource as the client list.

Then set the OnClick method of the combo box to fill the form. In this case, when random stuff is typed into the combo box, it will return the Item Not iIn The List error, but thats ok, no dramas there.

The issue with the add button seems to be that your using the value of the combo box in the New User creation..

Can you post up your db?

 

[zakster]

Thanks for your quick reply.

I can't post up the db due to size issues - slightly too big, even after a compact and repair, and zip.

There is no issue with 'Add' button as such. Basically the issue is that once the user starts to fill out the combo box and then tabs away without completing it - then Access pipes up and says that you can't leave it as null because it is a required field.

I might have to try making it an unbound field - but when would be the appropriate time to set the client field - I don't really see how the OnClick method for the combo box would be the right time.

Hope some of that makes sense.

 

[John Big Booty]

Why not use the "On Not In List Event" to pop up a message box to advise the user to Double click to add a new client.

   MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue

Then in the "On Dbl Click" event us the following code or a variation there of.

    Dim lngCombo0 As Long

    If IsNull(Me![Combo0]) Then
   
    Else
        lngCombo0 = Me![Combo0]
        Me![Combo0] = Null
    End If
    DoCmd.OpenForm "FRM_Name", , , , , acDialog, "GotoNew"
    Me![Combo0].Requery
    
     Forms![FRM_IstFrmName]![Combo0].Requery
    If lngCombo0 <> 0 Then Me![Combo0] = lngCombo0

Exit_Combo0_DblClick:
    Exit Sub

Err_Combo0_DblClick:
    MsgBox Err.Description
    Resume Exit_Combo0_DblClick

 

[zakster]

Thanks - I'll give that a try now

 

[Zaeed]

The reason is that you have the control source of the combo box as the table. So when you start to type into it, it is making a new record. In your table properties you must have the Is Required set as True. That is the issue.

You really should make it unbound.

Then simply in the Click event you would have something similar to below.

clientID = comboBox.Value

SQL = "SELECT * FROM quoteTable WHERE quoteTable.clientID = clientID"
doCmd.runSQL SQL

 

[John Big Booty]

There's no need to make the Combo unbound if the form and associated code is constructed correctly.

 

[Zaeed]

it doesnt require as much coding

 

[John Big Booty]

it doesnt require as much coding

That is not a good reason for making a decision on the design of the form. The primary concern is the functionally and ease of use, of the form, required by the user.

 

[Zaeed]

Yes, and by reducing the required code and eliminating the issue of writing directly to the table, makes things easier, and reduces the number of things that can go wrong.

By using unbound controls you also gain more control over what is happening.

 

[zakster]

Ok - I tried that code and still ran into issues....setting the combo box to null in the else statement resulted in the same "this is a required field error".

I then tried to change it to something like this...

Private Sub cboClient_DblClick(Cancel As Integer)
  DoCmd.OpenForm "frmContact"
  DoCmd.GoToRecord , , acNewRec
  Me.cboClient.Requery

Exit_cboClient_DblClick:
    Exit Sub

Err_cboClient_DblClick:
    MsgBox Err.Description
    Resume Exit_cboClient_DblClick
End Sub

But I get the error message...

2118: You must save the current field before you run the Requery action.

I also get this error message if I place the requery on the form_activate event.

 

[zakster]

Zaeed - if i am to used unbound controls - at what point do i actually create a record - and how do I do it?

I still don't understand which click event you are talking about? Could I have a button 'Create' that does all of the data validation and then saves the record?

If I do it this way - how will my subforms work? Don't I need to know the parent id number before I can add records to the subform?

 

[Zaeed]

It depends on what your form is doing.

The way i would suggest you do it is this.

Have your combo box populated by a query, that query has column 1 as clientID and cloumn 2 as their name. Set the comboBox to have 2 columns, bound to column 1, and with column widths as 0cm;2cm. This would result in a combo box exactly the same as what you have, however, whatever you type into has no effect on anything.

On the click event of the combo box (User selects a name from the list), you would have coding which fills an integer variable with comboBox.Value, since we made the comboBox bound to column 1 (clientID) the .Value of the comboBox is clientID. You then use that variable in a SQL statement, such as SELECT * FROM quoteTable WHERE clientID = randomVariable <- you could simply put comboBox.Value here if you wanted, same thing.

You would then set the Record Source of the form to that SQL statement. This would the fill the form, based on what is selected in your combo box.

As for creating a new User, I would do that with a popup form, which did the same thing when a save button is clicked, (close form and set record source)

If you could maybe make a scaled down version of your db and post up that. Take out everything thats not relevent to your current issue (forms tables etc) It would make it easier

 

[John Big Booty]

Ok - I tried that code and still ran into issues....setting the combo box to null in the else statement resulted in the same "this is a required field error".

I then tried to change it to something like this...

Private Sub cboClient_DblClick(Cancel As Integer)
  DoCmd.OpenForm "frmContact"
  DoCmd.GoToRecord , , acNewRec
  Me.cboClient.Requery

Exit_cboClient_DblClick:
    Exit Sub

Err_cboClient_DblClick:
    MsgBox Err.Description
    Resume Exit_cboClient_DblClick
End Sub

But I get the error message...

2118: You must save the current field before you run the Requery action.

I also get this error message if I place the requery on the form_activate event.

Set the field property to "Required" = No. You can then do a logical test when the focus moves to a new record to ensure that your user selects a valid client.

 

[zakster]

Okay I have finally got rid of all associated error messages

But now how to set the client in the combo box to be the new client created in the pop up window?

This is the double click event code....

Private Sub cboClient_DblClick(Cancel As Integer)
  Dim clientValue As Long

  If Not IsNull(Me![cboClient]) Then
    clientValue = Me![cboClient]
    Me!cboClient.Value = Null
  End If
  
  DoCmd.OpenForm "frmContact"
  DoCmd.GoToRecord , , acNewRec
  
  Me![cboClient].Requery
    
  If clientValue <> 0 Then Me![cboClient] = clientValue

Exit_cboClient_DblClick:
    Exit Sub

Err_cboClient_DblClick:
    MsgBox Err.Description
    Resume Exit_cboClient_DblClick
End Sub

 

[Luddite Lad]

You should find that the client that has been added will now be available for selection from the Combo Box.

 

[zakster]

You should find that the client that has been added will now be available for selection from the Combo Box.

Yes that's right it is available in the combo box - but I want to automatically select the new client for the client - so they don't have to add the client and then select the client.

Any idea how to do this?

 

[Zaeed]

will this particular add client button be specific to this form, or is it used elsewhere?

 

[zakster]

I'm not actually using the add client button anymore - just double clicking to add a new client. So it is definitely specific to the form.

 

[Luddite Lad]

Yes that's right it is available in the combo box - but I want to automatically select the new client for the client - so they don't have to add the client and then select the client.

Any idea how to do this?

I'm sure it can be done, but I must admit, I've never put any thought into actually doing it