NOTINLIST problem 1

[jamespeters01]

I am in the middle of creating a quotation database.
My main form is where all the customer details are entered. My problem is with the address details. The Customer name is set as a combo box and the rest of the address is set a text boxes. When a customer (which is already in my table(customer list))is selected the address automatically fill in the text boxes....this works fine....its when a new customer needs to be added, I have the problem. I have set the NOTINLIST to run a macro which opens my "add new customer" form, which works fine. I fill in the new details and close the form which returns me to my main form. The problem I have is because the main form hasn't closed down, it doesn't register the new address that I have added, and keeps sending me (through the NOTINLIST option) to my "add new customer" form. The only way that I can close the main form at the moment is if I select an existing customer, then just close it. Obviously this is not a user friendly way of working this database.

Any help would be gratefully appreciated.

Cheers

 

[ghubbell]

Hi James,
Please see Thread181-62967 for a decent example of working this exercise through... Gord
ghubbell@total.net

 

[jamespeters01]

Gord,

Thanks for the link.

I'm still having a little difficulty though. I keep getting a type mismatch error message after the "......is not an existing customer". Any ideas where I'm going wrong ?

Here is my script:


Private Sub combo1_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Combo1_NotInList

Dim lngcombo1 As Long
Dim newTxt As String, varBookMark As Variant

newTxt = NewData
newTxt = UCase(newTxt)

If MsgBox(newTxt & " is not an existing customer. Would you like to add it now?", vbYesNo + vbQuestion + vbDefaultButton1, "Catagory not found...&quot = vbNo Then
Response = acDataErrContinue
Else
DoCmd.OpenForm "ADD A NEW CUSTOMER", , , , , "GotoNew"
Forms![ADD A NEW CUSTOMER].[CUSTOMER] = newTxt
If IsNull(Me![Combo1]) Then
Me![Combo1].Text = ""
Else
lngcombo1 = Me![Combo1]
Me![Combo1] = Null
End If
If lngcombo1 <> 0 Then Me![Combo1] = lngcombo1
Me![Combo1].Requery
DoCmd.Close , , acSaveYes

Me![Combo1].Requery
Forms![Products].[Combo1].DefaultValue = newTxt
End If

Exit_Combo1_NotInList:
Exit Sub

Err_Combo1_NotInList:
MsgBox Err.Description
Resume Exit_Combo1_NotInList
End Sub

 

[ghubbell]

Hi James, give this one a try please:

Private Sub combo1_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Combo1_NotInList
Response = acDataErrContinue
NewData = UCase(NewData)
If MsgBox(NewData & &quot; is not an existing customer. Would you like to add them now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Customer not found...&quot = vbNo Then
Me.Combo1.Undo
Me.Combo1.Dropdown
Exit Sub
Else
DoCmd.OpenForm &quot;ADD A NEW CUSTOMER&quot;, , , , acFormAdd
Forms![ADD A NEW CUSTOMER].[CUSTOMER] = NewData
DoCmd.Close , , acSaveYes
Me.Combo1.Undo
Me.Combo1.Requery
Me.Combo1 = NewData
End If

Exit_Combo1_NotInList:
Exit Sub

Err_Combo1_NotInList:
MsgBox Err.Description
Resume Exit_Combo1_NotInList
End Sub

An assumption: your New Customer form has a customer number as an auto number or, no customer number at all and no other required fields when creating the record. Your combo1 is one column and a text field. (These are the signifigant differences between your case and the other sample...)
Gord
ghubbell@total.net

 

[jamespeters01]

Gord,

Thanks again.

You are correct, my New Customer form (as well as my main form) has no auto number or required fields.

My combo1 has all the columns from my customer list table (9 in all).

It's getting there (slowly but surely). My only problem is it now puts only the first line of the new addresses into my new customer form then straight back to my main form.

I need to be able to fill in the whole address, then be transferred back to my main form with the whole address now available in my combo box.

I tried taking out the DoCmd.Close,,acSaveYes, which let me fill in the other fields, but I had to go threw the process again before I could choose the new address inthe combo box.

 

[ghubbell]

OK James, Let's try this:

Private Sub combo1_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Combo1_NotInList
Response = acDataErrContinue
NewData = UCase(NewData)
If MsgBox(NewData & &quot; is not an existing customer. Would you like to add them now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Customer not found...&quot = vbNo Then
Me.Combo1.Undo
Me.Combo1.Dropdown
Exit Sub
Else
DoCmd.OpenForm &quot;ADD A NEW CUSTOMER&quot;, , , , acFormAdd
Forms![ADD A NEW CUSTOMER].[Customer] = NewData
Me.Combo1.Undo
Forms![ADD A NEW CUSTOMER].SetFocus
End If

Exit_Combo1_NotInList:
Exit Sub

Err_Combo1_NotInList:
MsgBox Err.Description
Resume Exit_Combo1_NotInList
End Sub

and in the ADD A NEW CUSTOMER form's on close event:

Private Sub Form_Close()
On error resume next
Forms!form1.SetFocus
Forms!form1.Combo1.Requery
Forms!form1.Combo1.Dropdown
End Sub

(Please change &quot;form1&quot; to the name of your first form.)

Should suit your needs prettyyyy close! Thanks James, Gord
ghubbell@total.net

 

[jamespeters01]

Hi Gord,

I followed your advise. It now lets me fill in the whole address which is great, but when it sends me back to my first form, it still doesn't recognise the new customer address until I go through the whole process again.

Any further ideas ?

Thanks

JAMES

 

[ghubbell]

Good morning James,

This would have to do with the requery of the first form from the second.

Private Sub Form_Close()
'On error resume next '&quot;Commented&quot;to see if we made an error
Forms!form1.SetFocus
Forms!form1.Combo1.Requery
Forms!form1.Combo1.Dropdown
End Sub

and give this a go please. Gord
ghubbell@total.net

 

[jamespeters01]

Good morning Gord,

My bad....it was a type error by me.....doh !

It works great, but just a couple of queries.
It automatically puts the Customer name in CAPS, any chance of changing this ?
And, when it returns to my first form, I still have to type in the customer for it to be found (although it now definately is there!). Any chance that the new address details I have just entered in my new customer form can automatically come up in the original (first form) ?

Thanks for all your help


James

 

[ghubbell]

No prob!

Ok remove this line: NewData = UCase(NewData)

and I'm not too sure if this will fly but try:

Private Sub Form_Close()
'On error resume next keep commented to check for error
Forms!form1.SetFocus
Forms!form1.Combo1.Requery
Forms!form1.Combo1 = Me.Customer '<----
'Forms!form1.Combo1.Dropdown 'if above works, we don't need
End Sub

Maybe....Maybe not....! Gord
ghubbell@total.net