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

[Mik2045]

Hi,



I'm a bit lost in Access 2010.

I'm trying to create a basic form with 3 fields, First Name, Last name and Phone number.

I'd like to be able to type in one of the values and it will open up a record in a form. While the record is open I'd like to be able to add or edit data.

How can I do this?

[June7]

You want to type in this value where - in a form? Then open another form? You want to open an existing record or create a new record?

[Mik2045]

I'd like to open an existing record based on a value on a form I created that has the three text fields (First Name, Last Name and Phone Number).

I'd like to be able to enter only one of the fields such as Phone number and it will pull up a record based on that phone number.

[June7]

Here is one method http://datapigtechnologies.com/flash...tomfilter.html

[Mik2045]

I don't think this is what I want. I already something similar to this. I'd like to search a name for example and it will pull up a record that's based on another form.

[June7]

Don't understand 'based on another form'.

The same technique can be applied to open another form or report. Review

http://datapigtechnologies.com/flash...mtoreport.html

[Mik2045]

This was what I was looking for:
http://www.youtube.com/watch?v=OAd9pnPJTPM

I'm having this problem though after I set it up.

I'm using combo boxes instead of the text boxes though. After I type in a First and Last Name, I click search and it pulls up their record in a form like I wanted it to. Then, I close it out and try another name and it works. But when I go back to search for the first record, somewhere during this process, it changed to the CustomerID of the 2nd record I searched for.

Weird right?

I have no idea why it's doing this. Any clue?

[June7]

Controls used to input search criteria must be UNBOUND, otherwise, whatever you type in to search for is actually changing data in record.

[Mik2045]

I made them UNBOUND but now when I search, they pull up a blank form.

Any idea why this is happening?

It only pulls up the correct record when it's BOUND.

[June7]

I would have to review combobox properties (RowSource, ColumnCount, ColumnWidths, BoundColumn) and the query the form is bound to. You can either enumerate them in post or provide the file. Follow instructions at bottom of my post.

I have difficulty viewing YouTube. Is the tutorial like the DataPig one? Are you using a parameterized query as the form's RecordSource? Did you try the DataPig design?

[Mik2045]

Okay. I will be attaching it.

DB.zip

Also, I was having a problem in that exact form. I believe I have subforms on that form. How do I connect the forms together so that all of the CustomerID fields on the form will be the same? At the moment, only the first CustomerID field is being filled and not the other 2.

The CustomerID is the Primary Key and it has a relationship with the other Primary Keys. It may be how the relationships are set up. I'm not sure. If you have time, please take a look at that.

Thanks man. Hope you can solve my problem. I'll be greatly happy if you do.

[June7]

There were no records in GuestT so I entered some for testing.

I tested the NewRegistration form and the filtering works.

What form are you having issues with?

Can't link tables on autonumber fields. Primary key can be autonumber but the related foreign key must NOT be autonumber.

[Mik2045]

I was referring to the Returning Guest form.

I think you were referring to the combo boxes on top of the New Registration, which I know works fine.

Thanks for the solution for the linking tables. I fixed them.

EDIT:

Now I'm having a problem with the combo boxes at the top, which used to work. I think it's because I added 2 more subforms and there might be a problem with the relationships but I deleted the the form that had no relationship with the CustomerID but that still didn't fix it. After trying to re-add them the 3rd option to find a record doesn't show up.

I remember a while ago I found a solution to that problem but I was confused on how to fix it.

EDIT:

Never mind I fixed it. ^^

EDIT:

Wow the macro I created works to find the record. But, only by running the macro by double clicking it and typing in a name.

After double clicking it, I type in a first name, then it opens up the record in form view. So, now I need to fix it so that it works properly by clicking the command button on the Returning guest Form.

Any idea why the command button won't do it?

EDIT:

WOW! I DID IT!

I had to have all of the fields filled out to pull the record.

Is there a way to only type in one field?

I think it's a called a Wildcard but that's related to queries.

[June7]

I would not have multiple forms for New, Old, Returning guests. I would use only one form to enter new record as well as to view existing.

So why have the ReturningGuest form at all? It just opens the NewRegistration form anyway which has search controls on it. Create a button to 'Add New Record'.

I think you might find these comboboxes to search on name parts confusing. What if you have several John's, Mary's, etc. and many Smith's?

[Mik2045]

I'm just trying to follow the way my boss wants it.

I see what you're saying though and I will talk to him about it.

Also, I already have a button to add a new record on the Startup. But, I can also create one on the registration form I suppose.

I just came up with an idea. To solve that problem with multiple John's, Mary's etc. I can hopefully require the user to enter at least 2 fields, such as the first and last name or phone number only (since it will only link to one person) so it can pull up the correct record.

EDIT:

Okay, now I have a button that will require the user to pull up a record based on all the fields and I have one that will only require them to type in a Mobile Phone #. Now I will try to do one with only First and Last Name and possibly ones with different combinations.

EDIT:
For some reason it's not working when trying to work with only the last name. I'm not sure but I think it's because of the combobox and the where condition I have.

EDIT:
Wow! I got it working by playing around a bit with the where condition.

Okay. I'm done with the Returning Guest form.

Thanks very much for your help.

Now, I need to focus on the advanced search dilemma I'm having in the other thread. Your help in that issue would greatly be appreciated!

EDIT:

Awww man! When I just realized when you click the search buttons it's only pulling up the first record and not based on what I typed in.


I just made an edit to the Where condition on the Mobile Phone macro which, now works fine on the form. Now, I need to fix the last name macro and first/last name macro again.

EDIT:

Nevermind. I just made them Text boxes and all as they're supposed to now. But, is there a way to make whatever you type in a LIKE criteria? I'd like this so that just in case we don't spell a last name correctly, we can still pull up the record with maybe the first 3 or 4 letters of their name.