Prompted Form

[misuser2k7]

I have created a form named 'CustIDpromptform' which has a textbox to enter customerid (long integer) and a command button which is supposed to open a second form which displays customer contact information when a customerid is entered in the textbox and the command button is clicked.

The display form (second form) is based on a simple query called 'custInformationQuery':

Select * from custInformationTable where customerid = Forms!CustIDPromptForm!customerid;

The table 'custInformationTable' has fields customerid (PK), customerName, customerAddress, customerPhone, customerEmailAddress. These fields are control sources for the textboxes in the second form.

The command button is coded to open second form called CustContactInformationForm.

Dim customerid As Long
If customerid = dlookup(customerid, “custInformationTable”) then
Docmd.openform “CustContactInformationForm”
Else: MsgBox("Check Customer ID and try again")
End if

But even when a valid customerid (with a complete corresponding customer record) is entered and the command button is clicked, only the "Check Customer ID and try again" message is displayed.

However, if the query (custInformationQuery) is run directly it prompts for textbox value of the customerid and shows/returns complete customer record associated with the customerid.

Is there a way to ensure the second form opens up displaying the information like the query it is based on does?

Thank you for your help.

 

[Darrylles]

What is the name of the textbox where you enter 'customerid'?
You define a 'customerid' variable as long, but never assign a value to it.

Try:

Dim customerid As Long 

customerid = textboxname

IF customerid = dlookup......

In any case, you are depending on the user 'remembering' EVERY customer id!
Why not show them a list of possible customer names (rather than remembering numbers)?

On form 1, use a combo (cmbCustomer) with it's rowsource = to:

	rowsource = SELECT customerid, 
	           customerName 
            FROM   customerInformationTable;

When a name is selected in the combo, it's value will now be the customerid.

In the combo <OnChange> event, use this:

	DoCmd.OpenForm "Form2", , , "[customerid]=" & cmbCustomer

Which tell's it to open Form2 and set that form's customerid equal to what is selected in the combo.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[misuser2k7]

Darrylle,

Thank you for your suggestion.

I already have a combo box which shows a list of customers by their last name and opens the second form based on the user's selection of the customer last name from the combo box content.

The problem with using the combo box is that multiple customers can have same last names and each of them will have to be selected to find the right customer.

The lookup by customerid will be done based on the customerid given by the customer so there is virtually no room for ambiguity unless the customer gives the wrong id but this mistake can be corrected by cross checking the customer information with the customer.

The name of the customerid textbox in the first and second forms is 'txtcustomerid'.

I don't understand what exactly you mean by "never assign a value to customerid textbox". The customerid textbox in the first form is unbound and carries a "value" in the second form because the second form is bound to the 'custInformationQuery'.

Also, I worked with a copy of the 'customerInformationTable' and 'custInformationQuery' based on the copy of the table in which the customerid had the 'short text' datatype and the second form worked as intended.

I was wondering if this might be a datatype issue since the second form works when the customerid has 'short text' datatype but does not when the customerid has 'long integer' datatype.

Again, thank you for your help.

 

[Darrylles]

Mis,

1) Change your combo so that it displays the FULL, UNIQUE customer name - not just the last name - why only use last name?
2) "never assign a value to customerid textbox": you define a VBA variable called 'customer_id' - as data type LONG. You then NEVER give it a value!
I expect something like: 'customer_id = X', but your code does not do this, therefore, customer_id is ALWAYS empty!

If, 'customer_id' is also textbox name, why do you define a variable called 'customer_id'?
Your form will be confused. Which customer_id do you want it to use - the textbox on the form or the variable that you have created in your code?

Remove 'dim customer_id as long' from your code and see if it makes a difference.

N.B. Get used to using a prefixed naming convention for ALL form objects and ALL variables e.g.:
frmMyForm
qryMyQuery
txtMyTextBox
cmbMyCombo
lblMyLabel
lngMyLongVariable
intMyIntVariable
strMyString

That way - you know the data-type / form object type simply from it's name!

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[misuser2k7]

Darrylles,

I found the solution for this issue.

I simply entered [Select * from custInformationTable where customerid = Forms!CustIDPromptForm!customerid;] in the recordsource of the second form instead of entering the query's name in the recordsource.

Now both forms work as intended.

Thanks for your help.