Help with Where condition with a concatenation

[Stephd8459]

Hi All,

I'm trying to create a command button that opens a second form and when the second form opens have a matching condition.

So basic code would be

Private Sub cmdOpenfrmContacts_Click()
  Dim strWhere As String
 
    strWhere = "[Contact] = '" & Me.Contact & "'"
 
  'Open the form
  DoCmd.OpenForm "frmContacts", acNormal, , strWhere
End Sub

The problem is that on the form I want to open 'frmContact' the match would be on 2 fields [FirstName] and [LastName]

So the code I was trying

strWhere = (Forms!frmContacts.FirstName & " " & Forms!frmContacts.LastName) Like "" * ""  & Me.Contact & "*"""

And this didn't bring a match....

I tried a second option

Private Sub cmdOpenfrmContacts_Click()
  Dim strWhere As String
  Dim strName As String
 
strName = Forms!frmContacts.FirstName & " " & Forms!frmContacts.LastName
strWhere = strName Like "" * ""  & Me.Contact & "*"""
 
DoCmd.OpenForm "frmContacts", acNormal, , strWhere

The problem with the above code based on debug print was that unless I put the open Form command before the strName line strName remained null... If I put the OpenForm before the strName then the matching didn't occur.

I'd appreciate it if anyone has a suggestion. I looked at creating a field FullName in a query and then using that as the match field but I haven't had much luck with that either... I don't have a relationship between the tblContacts which the form is based off and the main form I'm opening it from.

thanks,
Stephanie

 

[vbaInet]

No need for too much code. Have a look at this:

http://baldyweb.com/wherecondition.htm

 

[ghudson]

not tested but try...

strWhere = (Forms!frmContacts.FirstName AND Forms!frmContacts.LastName) Like "" * "" & Me.Contact & "*"""

 

[Stephd8459]

gHudson, tried the code and strWhere remains null


Paul, my question on the example on your site would be the syntax of the concatenation of FirstName and LastName

I tried the below and get and Complie error

'Expected: end of statement' highlighting " &[LastName] Like '"

 DoCmd.OpenForm "frmContacts", acNormal, , "[FirstName]& " " &[LastName] Like '" & Me.Contact & "'"

 

[Stephd8459]

So one additional note...
I tried Paul's suggestion with

DoCmd.OpenForm "frmContacts", acNormal, , "(Forms!frmContacts.FirstName And Forms!frmContacts.LastName) Like '" & Me.MonsterContact & "'"

and the value of Forms!frmContacts.FirstName And Forms!frmContacts.LastName is null... so I'm not sure why I am getting no value when I concatenate the two fields... If I pull just First Name I get the first value in the table

 

[vbaInet]

I'm not Paul, but you may come across him someday. Pbaldy is his username on here.

I don't see why you're using LIKE because you're not using any wild characters, so this would suffice:

 DoCmd.OpenForm "frmContacts", acNormal, , "[FirstName] = '" & Nz(Me.Contact, "") & "' OR [LastName] = '" & Nz(Me.Contact, "") & "'"

 

[Stephd8459]

vbaInet,
apologize... you sent me to Paul's sight and I just jumped to that... lol

let me give this a try

 

[vbaInet]

Haha! No worries

By the way, I think you should be using the ContactID, not the FirstName or Surname.

 

[Stephd8459]

so to use ContactID I'd need a relationship to the Contact table to make that work, at least I think I would...

The issue is that my Contact table is set up very specificlly to be connected to Active directory to extract emails andaddress info for contacts. Everytime I try to make a relationship to that table I seem to break the connection with AD

 

[vbaInet]

The problem is two contacts may have the same surname and forename, it's very possible. So yes you would need to create a join with the Contacts table.

If you're having difficulty with that then your best option would be to open the form and display ALL the records that match that surname and forename.

Here's a link:

http://baldyweb.com/Bookmark.htm

From Paul again

 

[Stephd8459]

thanks for the link

That worked for what I needed

 

[vbaInet]

You're welcome.