Dlookup errors ---- says not optional

[imrosie]

Hello all experts,

I'm a newbie and I tried, but unsuccessfully to make the Dlookup statement optional...Help. Here's my code:

Private Sub Customer_ID_NotInList(newdata As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If newdata = "" Then Exit Sub
msg = "'" & newdata & "' is not in the list." & cr & cr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Add an Order and Details", , , , acAdd, acDialog, newdata
End If
result = DLookup("[custname]= " & Chr(34) & newdata & Chr(34))
If IsNull(result) Then
Response = acDataErrContinue
MsgBox "Name is not found, please search again!"
Else
Response = acDataErrAdded
End If
 
End Sub

error msg says "argument not optional" thanks in advance.

[puppydogbuddy]

Rosie,

Your syntax for the DLookup is incorrect. Also, In your case, it seems it should look like the following because your procedure is named Customer_ID_NotInList:

Replace the generic object names I used for illustrative purposes with the actual names in your application.


if CustomerID is text
---------------------------

DLookup("[CustomerID]", "YourCustomerTable", "[CustomerID] = '" & newdata & "'"

if CustomerlD is numeric:
----------------------------------

DLookup("[CustomerID]", "YourCustomerTable", "[CustomerID] = " & Val(newdata) 'note Val function used because newdata defined as string

[imrosie]

Rosie,

Your syntax for the DLookup is incorrect. Also, In your case, it seems it should look like the following because your procedure is named Customer_ID_NotInList:

Replace the generic object names I used for illustrative purposes with the actual names in your application.


if CustomerID is text
---------------------------

DLookup("[CustomerID]", "YourCustomerTable", "[CustomerID] = '" & newdata & "'"

if CustomerlD is numeric:
----------------------------------

DLookup("[CustomerID]", "YourCustomerTable", "[CustomerID] = " & Val(newdata) 'note Val function used because newdata defined as string

Greetings Puppydogbuddy,
I really appreciate your help. I did change the code as you suggested.
The real problem here is I'm setting the wrong code for what I really need. I have several fields on the 'customer search form' (typical, cust name, add, phone, cell, email, etc). The cust name control is based on a select statement (SELECT [CustomerID], [LastName] & ", " & [FirstName] FROM Customers ORDER BY [LastName] & "," & [FirstName];) which is why changing just the 'Dlookup' wouldn't work. I want all controls, except cust name to be false until the name is either found (existing list) or not in list answered yes....to add a new customer. Then all field would be 'true' to continue adding the information. If a customer is found in list, then I need already stored data to populate other controls. I've been working on this all week and can't get it all working..

The DoCmd.OpenForm "Add an Order and Details", , , , acAdd, acDialog, NewData - isn't right either....because once I get the form filled out, I need the 'Add and Order and Details form to open (possibly from a begin new order button), with it's form controls filled in with the customer data.

A mouth full, but that's what a newbie is trying to do. Seems like I've got some pieces but can't tie it all together....any help would be greatly appreciated. thanks again...

Again, here's the code after I put in your suggestion:
Private Sub custname_NotInList(NewData As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub
msg = "'" & NewData & "' is not in the list." & cr & cr
msg = msg & "Do you want to add new customer?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Add an Order and Details", , , , acAdd, acDialog, NewData
End If
result = DLookup("[custname]", "imageInventory", "[custname] = " & Val(NewData))
If IsNull(result) Then
Response = acDataErrContinue
MsgBox "Name not found, please try again!"
Else
Response = acDataErrAdded
End If

End Sub

Rosie in left field....thanks

[puppydogbuddy]

You are correct. the DLookup is looking up values in your table, so if custname is not in (bound to) the table, it won't find it. The only way to make the "not in list" work with custname is to have a parse function that will break custname into its bound components and do the lookup and add in the background via the bound component. Otherwise, you have to use one of the bound controls for the Dlookup.

If you want to go with the parse function, I can probably find one for you. Let me know.

[imrosie]

You are correct. the DLookup is looking up values in your table, so if custname is not in (bound to) the table, it won't find it. The only way to make the "not in list" work with custname is to have a parse function that will break custname into its bound components and do the lookup and add in the background via the bound component. Otherwise, you have to use one of the bound controls for the Dlookup.

If you want to go with the parse function, I can probably find one for you. Let me know.

Wow that sounds great...if you have a sample, I will certainly try to follow it. As for my overall goal with this app, please see the new notes I just put in...thanks some much

Rosie

[puppydogbuddy]

Rosie,
As we discussed, here is the code I wrote to handle the "NotInList" event with a FullName entry that uses a parse routine in the background that splits the fullname entered into first and last name, which are then used to update the source table. In contrast to your approach of using a DLookup, the fullName column in the combobox illustrated here is bound to a FullName field in the applicable table. This combined with setting the Limit to list property to yes, enabled the use of Access's internal functionality for searching the combobox. If you heed help in implementing this, just let me know.

Informative comments have been included with the code to help you follow what the code is supposed to do. The parse code in the "NotInList" is designed to parse two formats for entering the fullname:

{FirstName LastName}
{LastName, FirstName}

Option Compare Database
Option Explicit
 
Private Sub cboFullName_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String    'for capturing the parsed components ofFullName
 
'the fullname entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("The name" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
"Do you want to add it now?" _
, vbQuestion + vbYesNo, "Express")
 
' Background process for Parseing fullname entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then     'fullname entered----> First Last
       strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
       strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then  'fullname entered----> Last, First
        strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
        strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
        MsgBox "You entered the name without a separator between First and Last Name."
        Exit Sub
End If
 
 
'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
If intAnswer = vbYes Then
    strSQL = "INSERT INTO qMemberList(Fullname, FirstName, LastName)" & _
    "VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 
    MsgBox "The name has been added to the list." _
        , vbInformation, "Express"
    'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
    Response = acDataErrAdded
Else
    MsgBox "Please select a name on the list." _
    , vbInformation, "Express"
    Response = acDataErrContinue
End If
End Sub
 

[imrosie]

Rosie,
As we discussed, here is the code I wrote to handle the "NotInList" event with a FullName entry that uses a parse routine in the background that splits the fullname entered into first and last name, which are then used to update the source table. In contrast to your approach of using a DLookup, the fullName column in the combobox illustrated here is bound to a FullName field in the applicable table. This combined with setting the Limit to list property to yes, enabled the use of Access's internal functionality for searching the combobox. If you heed help in implementing this, just let me know.

Informative comments have been included with the code to help you follow what the code is supposed to do. The parse code in the "NotInList" is designed to parse two formats for entering the fullname:

{FirstName LastName}
{LastName, FirstName}

Option Compare Database
Option Explicit
 
Private Sub cboFullName_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String    'for capturing the parsed components ofFullName
 
'the fullname entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("The name" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
"Do you want to add it now?" _
, vbQuestion + vbYesNo, "Express")
 
' Background process for Parseing fullname entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then     'fullname entered----> First Last
       strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
       strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then  'fullname entered----> Last, First
        strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
        strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
        MsgBox "You entered the name without a separator between First and Last Name."
        Exit Sub
End If
 
 
'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
If intAnswer = vbYes Then
    strSQL = "INSERT INTO qMemberList(Fullname, FirstName, LastName)" & _
    "VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 
    MsgBox "The name has been added to the list." _
        , vbInformation, "Express"
    'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
    Response = acDataErrAdded
Else
    MsgBox "Please select a name on the list." _
    , vbInformation, "Express"
    Response = acDataErrContinue
End If
End Sub
 

PuppyDogBuddy,

Thanks so much......I've been away from a computer for the past 4 days....(I know that's hard to believe)....so I'm just reviewing your post. I'm going to reveiw this carefully and attempt to get that background parse working in mine. Thats seems like it's a jewel...I'll get right back to you when it's working. thanks again.

Rosie

[puppydogbuddy]

Rosie,
Also keep in mind that you do not need to do a DLookup if you bind the fullname to a field in the table where you are storing the parsed firstname and last name. Access will do an "internal lookup" for you.

[imrosie]

Rosie,
Also keep in mind that you do not need to do a DLookup if you bind the fullname to a field in the table where you are storing the parsed firstname and last name. Access will do an "internal lookup" for you.

Hello again Puppydogbuddy,

Well, I tried the code it's working except for the acDataadded part. After reading through your code, I pretty much left the variables the way you declared them. Here's what mine looks like:

Private Sub CustomersName_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String 'for capturing the parsed components of FullName
'the FullName entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
"Would you like to add it?" _
, vbQuestion + vbYesNo, "Express")

' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then 'FullName entered----> First Last
strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then 'FullName entered----> Last, First
strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
MsgBox "You've entered the name without a comma between the first and last Name."
Exit Sub
End If
'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
If intAnswer = vbYes Then
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
"VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

MsgBox "The name has been added to the list." _
, vbInformation, "Express"
'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
Response = acDataErrAdded
Else
MsgBox "Please select a name on the list." _
, vbInformation, "Express"
Response = acDataErrContinue
End If
End Sub

I'm not sure on what line I was suppose to put the 'acDataAdded on. When the 'NotInList' kicks in, the I answer yes to add the 'new' name, but instead of it showing up in the list,it stays in the focus of the combo box and the lists drops down, but the new name isn't in it to be selected. then it goes into a loop, data never get requeried and the focus doesn't move. It keeps asking if you want to add the new name to the list over and over again.......I can't get past it.

As you suggested, I set 'yes' to limit to list, I added FullName to my Customers table. I didn't bound the combo box to the (called CustomersName) to the FullName because then I needed to search (query first and last names) the existing list of customers (over 5000) in the database.

Here is what my search query looks like on 'search form':
SELECT [CustomerID], [LastName] & ", " & [FirstName] FROM Customers ORDER BY [LastName] & "," & [FirstName];

Thanks again in advance for all you help, you've been great working with me! How now do I get past these issues. take care.

Rosie

[puppydogbuddy]

Hi Rosie,
the fix might be easier than first thought. The reason you are getting stuck in the loop with the acDataErrAdded is because, as you noted, the fullname is still in the combobox after the insert added it to the table. When the table change is refreshed back to the combobox...guess what....the fullname is already there.....thus the endless loop. The fix is easy. All you have to do is undo the entry to the combobox before the fullname is inserted and requeried via the acDataErrAdded as shown below:

If intAnswer = vbYes Then
 CustomersName.Undo       ‘undo entry to combobox so it won’t conflict with the table insert when the combobox is requeried
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
"VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
MsgBox "The name has been added to the list." _
, vbInformation, "Express"
 'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
Response = acDataErrAdded
 

Let me know if it works! Thanks.

[imrosie]

Hi Rosie,
the fix might be easier than first thought. The reason you are getting stuck in the loop with the acDataErrAdded is because, as you noted, the fullname is still in the combobox after the insert added it to the table. When the table change is refreshed back to the combobox...guess what....the fullname is already there.....thus the endless loop. The fix is easy. All you have to do is undo the entry to the combobox before the fullname is inserted and requeried via the acDataErrAdded as shown below:

If intAnswer = vbYes Then
 CustomersName.Undo       ‘undo entry to combobox so it won’t conflict with the table insert when the combobox is requeried
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
"VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
MsgBox "The name has been added to the list." _
, vbInformation, "Express"
 'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
Response = acDataErrAdded
 

Let me know if it works! Thanks.

Hi PuppyDogBuddy,

Well I added the 'CustomersName.Undo' line as you stated....It does add the name, but it still gets caught in the loop. The new name is still inthe combobox, if you attempt to move to the next control, it will tell you again that the name isn't in the list and ask "do you want to add it". However, when I close the form and reopen, the new name is in the list.

This same event happens over and over as I try adding a new name, the loop, then I have to close the form to choose from the list again.

Once I put in the 'CustomersName.Undo', the code wouldn't recompile. Instead I got the yellow bar over the code:

strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)

the error message from the form is:

'runtime error 5-invalid procedure call or argument....

Help, thanks again

Rosie

[puppydogbuddy]

Hi PuppyDogBuddy,

Well I added the 'CustomersName.Undo' line as you stated....Now it fails at the line with:
'strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)'

I'm getting a yellow bar in VB over the code. It's not compiling cleanly. Help

The undo is just on the wrong line. Try moving it to line 8 just after the warnings is set to true.

[imrosie]

The undo is just on the wrong line. Try moving it to line 8 just after the warnings is set to true.

Wow PuppyDogBuddy, this is great....it's now working great! I have a 2 more questions which are probably easy for you. I put in a new name (i.e. Tickleme Elmo)...when I select it from the list after the 'undo' it shows up in the combo box like 'Elmo, Tickleme. How can I get it to show as Tickleme, Elmo.

Secondly:
When I complete filling in the form I need to open an 'Order' form from here that auto populates the name, shipto, ect. fills from here...which command is best to accomplish 'Open Args'? From what I've read this seems to be the case, but not sure how exactly to do it. thanks again, this info has been invaluable. take care.

Rosie

Here's my the current code:
Private Sub CustomersName_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
Dim strSQL As String
Dim strFirstName As String, strLastName As String, strFullName As String 'for capturing the parsed components of FullName
'the FullName entered is not the table list; prompt user to ok adding it to list
intAnswer = MsgBox("" & Chr(34) & NewData & _
Chr(34) & " isn´t on the list." & vbCrLf & _
"Would you like to add it?" _
, vbQuestion + vbYesNo, "Express")

' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
If InStr(1, strFullName, ",") = 0 Then 'FullName entered----> First Last
strFirstName = Left(strFullName, InStr(strFullName, " ") - 1)
strLastName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, " "))
ElseIf InStr(1, strFullName, ",") > 0 Then 'FullName entered----> Last, First
strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
MsgBox "You've entered the name without a comma between the first and last name."
Exit Sub
End If
'insert parsed components captured in strFullName, strFirstName, and strLastName to the table
strSQL = "INSERT INTO Customers(FullName, FirstName, LastName)" & _
"VALUES ('" & strFullName & "', '" & strFirstName & "', '" & strLastName & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
If intAnswer = vbYes Then
CustomersName.Undo

MsgBox "The name has been added to the list." _
, vbInformation, "Express"
'acDataAdded causes the combobox to get requeried, the new item is selected, and the focus moves
Response = acDataErrAdded
Else
MsgBox "Please select a name on the list." _
, vbInformation, "Express"
Response = acDataErrContinue
End If
End Sub

[puppydogbuddy]

Rosie,
I am glad that combobox part is working. In regards to your questions:
1. Currently, your parsing routine is parsing FullNames entered with a comma as LastName, FirstName...and is parsing FullNames entered with a space separator instead of a comma as FirstName LastName....do you want to change the parsing to LastName FirstName when the FullName is entered with a space separator?
2. You don't need open args if you leave form1 open long enough to pass the values directly to form2.
Forms!form2!CustomerID.Value = Forms!form1!CustomerID

[imrosie]

Rosie,
I am glad that combobox part is working. In regards to your questions:
1. Currently, your parsing routine is parsing FullNames entered with a comma as LastName, FirstName...and is parsing FullNames entered with a space separator instead of a comma as FirstName LastName....do you want to change the parsing to LastName FirstName when the FullName is entered with a space separator?
2. You don't need open args if you leave form1 open long enough to pass the values directly to form2.
Forms!form2!CustomerID.Value = Forms!form1!CustomerID

Hi PuppyDogBuddy, thanks for you quick response.....
Yes (if you knew the folks that I have to provide this to) there would be a complaint that the lastname showed up first...It's fine to me, but my end users would not be happy.

Secondly are you stating that if I didn't close the 'Search' form and simply opened the second form to 'Orders', that it would automatically populate there?
I'm going to try that now....

Also, there is one more thing that I just noticed on my 'Search' form; when I select an old name (existing in database already with completed info (name, address, city, ect)) populated in the controls and then afterwards I add a new name to list....... the controls are still populated with the previous persons info....until I type over it....what's the best way to clear the controls.....'on Enter'?

thanks
Rosie

[puppydogbuddy]

Hi PuppyDogBuddy, thanks for you quick response.....
Yes (if you knew the folks that I have to provide this to) there would be a complaint that the lastname showed up first...It's fine to me, but my end users would not be happy.

Secondly are you stating that if I didn't close the 'Search' form and simply opened the second form to 'Orders', that it would automatically populate there?
I'm going to try that now....

Also, there is one more thing that I just noticed on my 'Search' form; when I select an old name (existing in database already with completed info (name, address, city, ect)) populated in the controls and then afterwards I add a new name to list....... the controls are still populated with the previous persons info....until I type over it....what's the best way to clear the controls.....'on Enter'?

thanks
Rosie

Rosie,
1. Ok. Will change parser for you by Monday..but reconfirm that you want space delimited to be parsed so that Left of space = LastName
2. No. You have put code (like the example I gave you) in the open event of Form2
3. Do you want all of the controls cleared or just certain ones?

[imrosie]

Rosie,
1. Ok. Will change parser for you by Monday..but reconfirm that you want space delimited to be parsed so that Left of space = LastName
2. No. You have put code (like the example I gave you) in the open event of Form2
3. Do you want all of the controls cleared or just certain ones?

Thanks PuppyDogBuddy, I'm very grateful.
1.) OK...I think your first delimiter (comma) was better. So let's stick with the comma, but I still need combo to show First, Last.

2.) I'm not real clear on where this should be in the code (my Form 2 is called 'Add Order' and Form 1 is called 'Search Customer'.)

Using your format of - Forms!form2!CustomerID.Value = Forms!form1!CustomerID
Should I code it like this:
Forms!Add Order!CustomerID.Value = Forms!Search Customer!CustomerID

I'm not sure on the 'Value' piece either. Can I use " " for the value so it inherits whatever the customer ID is?

3.) All the controls from the previous customer (includes, ship addr, bill addr, phone, fax num, email addr, city/state/zip, etc. ) thanks

Have a great weekend
Rosie

[puppydogbuddy]

Thanks PuppyDogBuddy, I'm very grateful.
1.) OK...I think your first delimiter (comma) was better. So let's stick with the comma, but I still need combo to show First, Last.

2.) I'm not real clear on where this should be in the code (my Form 2 is called 'Add Order' and Form 1 is called 'Search Customer'.)

Using your format of - Forms!form2!CustomerID.Value = Forms!form1!CustomerID
Should I code it like this:
Forms![Add Order]!CustomerID.Value = Forms!Search Customer!CustomerID

I'm not sure on the 'Value' piece either. Can I use " " for the value so it inherits whatever the customer ID is?

3.) All the controls from the previous customer (includes, ship addr, bill addr, phone, fax num, email addr, city/state/zip, etc. ) thanks

Have a great weekend
Rosie

Rosie,

#1
I am still confused on what you want for the Parser. Below is how the the fullname is input and what will be output from the parser. What I need to know is what format you are going to input the fullName into the combobox. Currently, the parser will provide the correct output for input formats 1 and 3 below. If I understand you correctly, you will be inputting fullName in either formats 1 or 4, and that being the case, I need to change the parse on input forrnat 4 so that it outputs John as FirstName and Doe as LastName. After the changes are put in input formats 1 or 4 will be the only input formats that will be parsed correctly.


input format -------------------------------- Current parsed output
------------------------------------------------ FirstName ----------LastName
1.Doe, John ------------------------------ John ------------------- Doe
2. John, Doe ----------------------------- Doe ------------------- John
3. John Doe ------------------------------ John ------------------- Doe
4. Doe John ------------------------------ Doe ------------------- John

__________________________________________________ _____________
#2
Yes, it would look something like shown below. .Value is the value currently stored in that control.

Private Sub Form_Load()               'load event for Order form
 
Forms![Add Order]!CustomerID.Value = Forms![Search Customer]!CustomerID
........continue same for other controls you want filled..note that names with spaces must be surrounded with brackets..................................................
 
'close "Search Customer" after parameters passed
DoCmd.Close acForm , "Search Customer",acSaveNo  
 
End Sub
 

__________________________________________________ __
#3
the controls should be cleared upon close of the Search Customers form as shown above ....acSaveNo.

[imrosie]

Hello Puppydogbuddy,

Thanks for your responses. Don't be confused.

On #1: You believe that currently, the parser gives the correct output for input formats 1 and 3, but it doesn't. That's what my request was for (to have 1 and 3). I'm not asking for 2 and 4. Instead once a new name is added to the list, its put in as Doe, John. Then when I select it from the list, it shows up in the combo box as Doe, John. That's what I'm experiencing.

On #2 and #3...... I'm getting a yellow bar over the first of the 3 'Forms!' lines of code. So it's not compiling, tells me it can't find the 'Add an Order and Details' form. Here's the actual code I put in:

Private Sub Form_Load() 'The load event for Order form
Forms![Add an Order and Details]!CustomerID.Value = Forms![Search Customer Record]!CustomerID
Forms![Add an Order and Details]!CompanyName.Value = Forms![Search Customer Record]!CompanyName
Forms![Add an Order and Details]!CustomerID.Value = Forms![Search Customer Record]!CustomerID

'........continue same for other controls you want filled..note that names with spaces must be surrounded with brackets.......................................... ........

'To close "Search Customer" form after the parameters are passed
DoCmd.Close acForm, "Search Customer", acSaveNo
End Sub

Sorry for the confusion in my previous communication..
Rosie.

[imrosie]

Hello Puppydogbuddy,

Thanks for your responses. Don't be confused.

On #1: You believe that currently, the parser gives the correct output for input formats 1 and 3, but it doesn't. That's what my request was for (to have 1 and 3). I'm not asking for 2 and 4. Instead once a new name is added to the list, its put in as Doe, John. Then when I select it from the list, it shows up in the combo box as Doe, John. That's what I'm experiencing.

On #2 and #3...... I'm getting a yellow bar over the first of the 3 'Forms!' lines of code. So it's not compiling, tells me it can't find the 'Add an Order and Details' form. Here's the actual code I put in:

Private Sub Form_Load() 'The load event for Order form
Forms![Add an Order and Details]!CustomerID.Value = Forms![Search Customer Record]!CustomerID
Forms![Add an Order and Details]!CompanyName.Value = Forms![Search Customer Record]!CompanyName
Forms![Add an Order and Details]!CustomerID.Value = Forms![Search Customer Record]!CustomerID

'........continue same for other controls you want filled..note that names with spaces must be surrounded with brackets.......................................... ........

'To close "Search Customer" form after the parameters are passed
DoCmd.Close acForm, "Search Customer", acSaveNo
End Sub

Sorry for the confusion in my previous communication..
Rosie.

Correction on #1: You believe that currently, the parser gives the correct output for input formats 1 and 3, but it doesn't. That's what my request was for (to have 1 and 4). I'm not asking for 2.

What's happening is that when a new name is added to the list, its put in as Doe, John. Then when I select it from the list, it shows up in the combo box as Doe, John. That's what I'm experiencing. thanks again.

Correction on #2 the error msg is 2450, it says it can't find my form 'Add an Order and Details'.

Rosie

[puppydogbuddy]

Hi Rosie,
If you are only going to enter Doe, John then your parse routine should look as shown below. This routine has nothing to do with how the name appears in your combobox list. It only determines how the parsed components are stored in the table. The row source query controls how it appears in the combobox list.

' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
 
If InStr(1, strFullName, ",") > 0 Then 'FullName entered----> Last, First
strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
MsgBox "You've entered the name without a comma between the first and last name."
Exit Sub
End If
 

In regards to #2
Correction on #2 the error msg is 2450, it says it can't find my form 'Add an Order and Details'


So it is working for all but that one??? Is that form a subform? A subform reference would be like this from your search form:

Forms![YourMainForm]![Add an Order and Details].Form!CustomerID.Value

If that is not it, then move your code to the Form_Open() event instead of the Form_Load() as you have it now.

[imrosie]

Hi Rosie,
If you are only going to enter Doe, John then your parse routine should look as shown below. This routine has nothing to do with how the name appears in your combobox list. It only determines how the parsed components are stored in the table. The row source query controls how it appears in the combobox list.

' Background process for Parsing FullName entry into first and last name
strFullName = Trim(CStr(NewData)) ' Change Variant to String
 
If InStr(1, strFullName, ",") > 0 Then 'FullName entered----> Last, First
strLastName = Left(strFullName, InStr(strFullName, ",") - 1)
strFirstName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, ",") - 1)
Else
MsgBox "You've entered the name without a comma between the first and last name."
Exit Sub
End If
 

In regards to #2
Correction on #2 the error msg is 2450, it says it can't find my form 'Add an Order and Details'


So it is working for all but that one??? Is that form a subform? A subform reference would be like this from your search form:

Forms![YourMainForm]![Add an Order and Details].Form!CustomerID.Value

If that is not it, then move your code to the Form_Open() event instead of the Form_Load() as you have it now.

Hello Puppydogbuddy,

I entered the new code, fixed the 'row source query'...ALL is working great on number one....thanks a million.

As for #2, No [Add and Order and Details] isn't a subform, but has a subform in it. However, the problem I think was that I had put it in the wrong place (in the 'loading' of the 'Search Form' )and it shouldn't have been there. I really need it in the command button (called Begin New Order to open the Order form on click)....So I moved it there on the Search form. Right now I can't test to see if it works or not because the compiler is still stuck on not being able to find the field CustomerID now.

Originally, it couldn't find the form (Add an Order and Details)... After I tried recompiling a few times, it suddenly can recognize the form, but not the field now. Weird. So once compiler can get pass that, I can see if the 'Begin New Order command button will work..

I'm going to try moving some brackets around those 'Forms' lines of code to see it that can fix it.

Here is what the 'Begin New Order' procedure code looks like now:
Private Sub BeginNewOrder_Click() 'The event to open the Order form with some controls filled in
Forms![Add an Order and Details]![CustomerID].Value = Forms![Search Customer Record]!CustomerID
Forms![Add an Order and Details]!CompanyName.Value = Forms![Search Customer Record]!CompanyName
Forms![Add an Order and Details]!FullName.Value = Forms![Search Customer Record]!FullName

'........continue same for other controls you want filled..note that names with spaces must be surrounded with brackets.......................................... ........
'To close "Search Customer Record" form after the parameters are passed
DoCmd.Close acForm, "Search Customer Record", acSaveNo
End Sub

[puppydogbuddy]

Hi Rosie,
I am glad the parsing/combobox issue is resolved. On the other issue, I hope you know that the Form_Load() and Form_Open() events I was referring to were the Load and Open events related to the [Add an Order And Details], not the Search Form. At any rate, placing the Code behind a button click is probably better from a logistics point of view....so I think that was a good idea.

In regards to the CustomerID compiler problem......you are probably getting that error because CustomerID was not included in the field list in the query that was the record source of the Search form and/or the CustomerID was not placed as a control on the Search Form. If you don't want to show the customerID on the SearchForm, just use a hidden textbox (textbox whose visible property is set to NO).

Let me know how it goes.

[imrosie]

Hi Rosie,
I am glad the parsing/combobox issue is resolved. On the other issue, I hope you know that the Form_Load() and Form_Open() events I was referring to were the Load and Open events related to the [Add an Order And Details], not the Search Form. At any rate, placing the Code behind a button click is probably better from a logistics point of view....so I think that was a good idea.

In regards to the CustomerID compiler problem......you are probably getting that error because CustomerID was not included in the field list in the query that was the record source of the Search form and/or the CustomerID was not placed as a control on the Search Form. If you don't want to show the customerID on the SearchForm, just use a hidden textbox (textbox whose visible property is set to NO).

Let me know how it goes.

Thanks for your response. Yes I was aware that you were referring to 'Add Order and Detail'. It was me, I messed up.

As for the CustomerID, it was included in the query (which supports the Search form) and is also one of the controls on the Search form....so I'm at a loss. If you have any suggestions, thanks in advance.

Rosie

[puppydogbuddy]

Thanks for your response. Yes I was aware that you were referring to 'Add Order and Detail'. It was me, I messed up.

As for the CustomerID, it was included in the query (which supports the Search form) and is also one of the controls on the Search form....so I'm at a loss. If you have any suggestions, thanks in advance.

Rosie

Hi Rosie,
Make sure CustomerID exactly matches the name property of the control in spelling and as to proper case. Make sure that the control on the Add form is enabled/not locked when parameters are passed. Make sure the AddForm has the proper settings ......Allow Edits = Yes, allow additions = Yes. Make sure both Forms are opened.