notinlist on Combo Box - how do I keep information current

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please can anyone advise as I'm a bit stuck,

I need to use the combo box on customer name in a subform to add new sites -
to ensure that if a customer has many sites the same customer name format is
used each time - however I can't get the update in the combo box to work when
a new customer name is required.

I've read through various ways on how to do this but I can't find a solution
for how to solve my problem so here goes,
I have used the code on NotInList as below which worked except for the fact
that it seems to save this single value each time a new one is entered.

This throws up errors due to the fact that all of the other fields are
mandatory or if I take this off then because the site ID is a primary key and
it can't be null.

How can I get this to run and stick to the current entry so that I can
update the whole record and then it gets added to the table?

for information:
I do also have a Goto new Record on the on Open of the subform

Private Sub Customer_Name_Combo_NotInList(NewData As String, Response As
Integer)
DoCmd.DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20

Dim db As DAO.Database
Set db = CurrentDb

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this Customer Name?", vbYesNo + vbQuestion,
"Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO TblCustomerProfiles (CustomerName) VALUES (""" &
NewData & """)", dbFailOnError
'Tell Access you've added the new value
Response = acDataErrAdded

Else

'The user clicked No - discard the new value
Me.Customer_Name_Combo.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

db.Close
Set db = Nothing

End Sub

Thanks for advice - it's all a bit new!
 
If you need to fill in more than one field in the NotInList event, it is
usually best to use a pop-up form to do the work then return to the
NotInList event when you close the form, saving all of the data you need to
enter. If you open the pop-up form using the acDialog argument it will cause
the NotInList event to pause until you close or hide the pop-up form.

Example:
If MsgBox("Not it list, add it?", vbYesNo+vbQuestion) = vbYes Then
DoCmd.OpenForm "frmMyPopup",,,, acDialog, NewData
Response = acDataErrAdded
Else
Me.cboMyCombo.Undo
Response = acDataErrContinue
End If

In this example the NewData is being passed to the form in the OpenArgs
argument. You could have the pop-up form check the value of this argument
when it opens and prepopulate that field in the form.
 
Hi Wayne,

Is there another way??- my problem is that the inital search is on site ID
and the combo I am struggling with is already on "add new site" pop up, seems
a bit messy to add another pop-up
I'm trying to use the Combo on customer name to ensure that the same format
is used when a new site is entered (each customer can have many sites)

thanks again for your advice
 
You could do it all in code, popping up Input Boxes for the user to fill in
each item, but this would probably be more aggravating than a single form
with all of the boxes popping up. Are you duplicating data by chance? If so,
you should only need the ID field from the associated table and link the
rest of the information in later when you need it. If the customer already
exists, you should be able to just store the customer ID. If the customer
doesn't yet exist, then you'll need to fill in all of the customer data.
 
Hi Hazel,

I'm not quite following your problem/solution paradigm. You need a
level of functionality for each level of result you seek to implement.
Something that might help in your current dilemma is to always show
NewData in the msgbox:

If MsgBox(NewData & "is Not it list, add it?", vbYesNo+vbQuestion) =
vbYes Then ...

If you are adding a site you don't need to bother trying to keep the
Company Name in view. It should not appear in the Site record. I'm
assuming a one-to-many relationship between Company Name and Site.

The NotInList event for CompanyName and the NotInList event for Site
should be totally independent streams of functionality.

HTH
 
Thanks for the advice both,

What I'm basically trying to do is use a combo box to validate that customer
names are always entered in the same format.
It seems that this is something that can't be done when the primary key is
based on another field.

I'll seperate for integrity purposes but I think even if I seperate the
customer name and site id onto a seperate table from the rest of the site
information and I link this by site ID I'm still going to get an error
because the site ID as primary key can't be empty at the time I add the new
customer name.

The only way I can see to do this from your advice is to add customer name
last - this is an illogical order of entry for the users so if anyone knows
of another way I'd be gratefull.
 
Back
Top