Setting form to a specific record

[anasazii]

Ok. I have a form that is based on a query, which I am using also for data entry. After entering the first and last names, I want to do a search on my db and make sure that the person is not already in there. (I'm doing this w/ an event procedure in the AfterUpdate event.)

I've gotten this part to work successfully, using a recordset and FindFirst. My question is now I want to be able to go to that record on my form and be able to update it, without the other records disappearing. I've tried Me.RecordSource, which somewhat works, but I lose sight of all my other records. Is there any other way to do this?

I'm still learning all this, so if you could help me w/some specific code it would be much appreciated.

Thanks for your help!
Janel

 

[LarryDeLaruelle]

Jane:

Put a combo box on your form linked to query that is the recordsouce of the form. (With the wizard enabled, select the third option on the first dialog screen -- something like "find a record on my form . . .".

I assume that you have a unique record id; bring that in as well as the last and first names; make the id field the bound column; select "Remember for Later use". Set the Autoexpand property to Yes.

This is more cosmetic than functional: Click on the elipses (...) next to the RowSource property of the combo box and modify the underlying query to concantenate the name -- Name: LastName & ", " & FirstName. You'll need to change the column number property and column widths property as well.

Make the combo the first tab position. (or use set focus in code)

When you (or the user) begins typing in a name and autoexpand will try to find a match from the underlying rowsource.

Hope that helps.
Larry De Laruelle
larry1de@yahoo.com

 

[anasazii]

Thanks Larry!
I was able to get this somewhat figured out with a combo box, but I'd really like to use just text boxes if at all possible. I have gotten the code to work, but I'm still having a problem.

First, I enter the f/lnames on a new record. Then AfterUpdate() catches whether or not the record exists (I'm doing this w/ a msgBox if that matters at all). If it does, I can go to it, no problem. However, Access still adds the other record (the names that were on the new record form) into db. Is there any way to cancel this from happening in the code?

Hope this makes sense...
Thanks again, Janel

 

[nickjar2]

If I have read this correctly maybe try using an unbound text box to enter the name rather than a field bound to the Name field.

Nick

 

[LarryDeLaruelle]

Jane:

I think Nick is pointing you in the right direction.

If you are using bound controls you will experience the problem you indicate of creating a new record.

Following Nick's recommendatation, add two unbound text controls to your form. Put the code you now have in the bound control's After_Update event in the After_Update event of the appropriate unbound control. Or, add a command button to your form (Find Record) and put the code in that control's On_Click event.

BTW, how are you checking for the existence of an existing record and what are you doing if one is found/not found?

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[anasazii]

Thanks guys! I was starting to try out unbound text boxes as well. It seems to be working pretty smoothly so far.

I'm checking for an existing record using FindFirst, with the criteria being the first and last name. If one is found, then I give the user the option of going to that record to edit or else starting over and entering another record. If a matching record isn't found then the user just continues on w/ entering the information.

Thanks again for the help,
Janel