Creating a search form that opens a record in form view in edit mode

[June7]

ReturningGuest search form is not working. Still combobxes, not textboxes. Did you provide the correct version of db? The values of comboboxes are the CustomerID but you have search criteria set up to use text values LastName, FirstName, MobilePhone. Either use the CustomerID field in search criteria or remove it from the combobox RowSource. Also, eliminate the combobox embedded macros.



Criteria with LIKE and wildcard would be:

[FirstName] Like [Forms]![ReturningGuest]![FirstName] & "*" And [LastName] Like [Forms]![ReturningGuest]![LastName] & "*" And [MobilePhone] Like [Forms]![ReturningGuest]![MobilePhone] & "*"

I changed the SearchByAll macro to this criteria and it runs without error but doesn't open form to the record. I really, really, don't like macros. This would be so simple with VBA or even the parameterized query demonstrated by the DataPig tutorial. Set the query as form RecordSource and use macro to open form but don't use the macro WHERE.

WAIT: finally got the macro WHERE to work, need quote marks:
"[FirstName] Like " & [Forms]![ReturningGuest]![FirstName] & "* And [LastName] Like " & [Forms]![ReturningGuest]![LastName] & "* And [MobilePhone] Like " & [Forms]![ReturningGuest]![MobilePhone] & "*"

However, you will find the results unsatisfactory when entering both name parts and they aren't a valid combination in the data. I would use a single combobox for name that concatenates the name parts:
SELECT LastName & ", " & FirstName AS CustName FROM GuestT;
Then use a query as the RegistrationF RecordSource and calculate CustName field in that query to concatenate the name parts.

The RealtorInformation subform on RegistrationF doesn't make sense - linking on HowDidYouHearAboutUs and RealtorCoID.

[Mik2045]

Sorry, that wasn't the latest DB.

Here it is.

Latest DB.accdb.zip

I wasn't so sure a bit what to with the realtor form or how to link it.

A lot of people may have been referred by a realtor and I wanted to keep track of that so that's why it was like that.

[June7]

I did some edits of my previous post as you were posting. Think my comments are still mostly relevant even though I haven't yet looked at the newer db.

[Mik2045]

I believe I got the search working using text boxes.

At the moment I have multiple macros for the search but I'd prefer all of them be in one or have only one search button on the form. Is that possible?

Do you have skype or MSN by the way? Haahaha

[June7]

The WHERE expression I showed works with a single command button.

However, again, the results will not be satisfactory with the name parts as individual parameters.

No Skype or MSN.

[Mik2045]

Okay I'll definitely be giving that a try. At the moment, I can't test it because I'm not at work and I don't have a PC.

I will give it a try tomorrow morning and provide some feedback on what happened. Thanks a lot.

[Mik2045]

Okay, when I tried your fixed where conditions I'm unable to save because the where conditions are invalid.

Also, I created a query using the like criteria you gave me but it's pulling up in a table. I'd like it to be able to pull up in a form. But I will try to play around and see if I can get it to.

Nevermind.

I used the same code I used as my Advanced Code. Works perfect!

Problem solved!