Where Condition in Open Form Command

[Srin]

I have a Datasheet form which has a text field "Customer" which is hyperlinked to open another form to populate Customer details. I am using MacroBuilder with the OpenForm command with the Where condition which is something like this

WHERE ="[Customer]=" &[Customer]

But this does not seem to work for this field. Can someone help me out with the right form to call it out. Thank you.

[The Customer field contains names that have text and also other special characters. Ex: "John Chemicals (U.S)"

[etorasso]

You need to add single tick marks around the [Customer] field to show it as text. The Where statement should look like this:

WHERE ="[Customer]= '" & [Customer] & "'"

[Srin]

Thanks etorasso,

This is working but not for all fields. In fields which have customer names such as just "John" or "John-Chem, it does not show anything.

Would "LIKE" instead of "=" better serve the purpose?
If so can you illustrate with an example.

Thanks again.

[ittechguy]

When working w/ text boxes, "LIKE" is preferred because it will match similar records wheras = must be an exact match. = is better used w/ combo boxes.

Now, one other thing to consider is you really should be doing this in vba. Trust me, I've spent nearly a month trying to get the macro builder to do what I needed it to do. I had to scrap it all and start learning VBA.

[Srin]

How should I go about doing it via the VBA route. I donot know how to access VBA or to write codes. It would be helpful if you could give me an illustration or guide me to a link where I can learn this. For now an example would be really helpful.

Thank you.

[ItsMe]

This example may get you started. The VBA code does not include the WHERE criteria, but it can be added after you get the command button working. I suggest using a new command button to ensure there is not any association with your button to a Macro.
https://www.accessforums.net/access/...tml#post296196

[Srin]

Hi, @ItsMe

I tried the VBA code on code builder. But I am still getting stuck on the same problem of defining the where condition.

It is not working on fields which have customer names such as just "John" or "John-Chem" or "Chem (Global)", it does not show anything.
Is there a workaround to this?

MY Code:

Private Sub Customer_Click()
DoCmd.OpenForm "frmCustomerContact", _
WhereCondition:="Customer= '" & Me.Customer & "' "


End Sub

[ItsMe]

What you are doing here is sometimes referred to as named parameters. I believe the syntax you have there is correct. However, named parameters have a specific use.

DoCmd.OpenForm "frmCustomerContact", _
 WhereCondition:="Customer= '" & Me.Customer & "' "

It is more common to place your arguments within the predefined parameters. You do this by separating your arguments with a comma. Use the hint provided as you type to understand the parameter.

DoCmd.OpenForm "frmCustomerContact", , , "Customer = '" & Me.Customer.Value & "' "

As mentioned, the = operator is used (in your case/example) to equate one operand with another. If it does not match exactly, no joy. Another approach, as mentioned, is to use the LIKE operator. This is acceptable for Text data types. The like operator will allow you to employ wildcards. In Access, use * as a wildcard.

DoCmd.OpenForm "frmCustomerContact", , , "Customer LIKE '*" & Me.Customer.Value & "' "
'or
DoCmd.OpenForm "frmCustomerContact", , , "Customer LIKE '*" & Me.Customer.Value & "*' "

[Srin]

This was very helpful for me to understand. It makes a lot more sense when I am typing the commas as to what each signifies. Thank you @ItsMe. I am beginning to see some light at the other end of the tunnel.

The command is working now, but only two names are not. They are of the form "Aren-Costa-Clarocose", "NGC", "Danfe" and "Actiwise Inc (Rapid Tech Ltd)"

I donot know the reason behind this. Is this totally unrelated to the command?

My New Code:

Private Sub Customer_Click()
If Not Me.NewRecord Then
DoCmd.OpenForm "frmCustomerContact", , , "Customer LIKE '*" & Me.Customer.Value & "*' "


End If
End Sub

[ItsMe]

The two wildcard examples I provided do not employ a third option. The examples demonstrate a search that might be defined as 'Ends With', as well as an example for 'Contains'. A third example might be explained as 'Starts With'

DoCmd.OpenForm "frmCustomerContact", , , "Customer LIKE '" & Me.Customer.Value & "*' "

Another issue may be the use of special characters. You are using VBA and need to be aware of special characters for VBA. For example, the = operator is used in VBA for equating as well as for assignment. When VBA sees it, it may consider it to be an operator. Also, you are using SQL. The following is SQL

Customer LIKE 'Company X*'

The parameter in the VBA method, .OpenForm, accepts an SQL statement as an argument.

So, you also have to be aware of special characters for SQL that are specific to Access. I suspect there may be an issue with the ( character and maybe the - character.

There are ways around this. For instance, every language will have an 'Escape Character' that can be employed. You would use an escape character to tell the compiler you are not using an operator or something else the compiler might misinterpret. Having said that, I do not allow for special characters in search forms. It would require a great deal of code to accept special characters in a search field.

[orange]

You may find this site and examples helpful.

[Srin]

Thank you both.@orange and @ItsMe,

I am now able to understand what you mean by not allowing special characters in search forms. I will stress on that fact to the user once I release this. But for me, I am not able to figure out a way for the already existing records due to this method of entry. I tried using the escape characters that you mentioned, but that ends up eliminating the other records which do not have them. Like you said it gets complex.

Till I figure that out, this remains "As is", or I can figure out something else in the meantime.

Thanks once again.