Find Record Using Form Field - Newbie Question 1

[amourdevin]

I have a very simple database where one of the fields always has a unique entry (not the key or ID field). I want to create a form where you enter a value contained in that one field, hit the enter key and all the details of the record show up. Further, I want all the fields for that record to be editable except for that one field that has the unique values.

Thanks in advance!

 

[Eupher]

This sounds like a job for the combo box wizard. Place your combo on your form, and at the first wizard prompt, select "Find a record on my form based on the value I selected in my combo box." That should get you started. Post back if you have more questions.

Ken S.

 

[amourdevin]

Maybe I should clarify what I meant. I envisioned several text boxes. One of the boxes would be solely to input a value to search a single field. There are hundreds of values in that field but each one is unique. The other text boxes would display the remainder of the entire record from the other fields and would be editable.

 

[Eupher]

I think I get the picture. And I stand by my original recommendation. If I understand you correctly, this is *exactly* the kind of situation the "Find a record..." combo wizard is built to address.

1) Build your form. Set the Record Source to the table or query you wish. Place your text fields on the form, but NOT the one you wish to seach on.

2) Place a combo box on the form, and select the "Find a record..." wizard. Step through the wizard, setting it up for your search field.

3) Set the combo's "Limit to List" property to "Yes" and its "Auto Expand" property to "Yes". (the wizard may do this automatically, but check it to make sure)

By using a combo instead of a textbox, you give your users the option of typing in the search value or selecting it from a drop-down list. Setting the limit to list property will prevent users from editing the data in that field: they can't add or remove items from the list, and if they type in something that's not in the list, Access will bark at them. The auto expand property will auto-fill the field as users type in the data, so they don't have to scroll down a long list to find their entry. You lose both these features (limit to list and auto expand) if you insist on a textbox for your search field. You could probably simulate those features to a certain extent with code, that's a lot harder route.

If I'm totally off base here and am misunderstanding what you are trying to do, post back with some more details.

HTH

Ken S.

 

[amourdevin]

You know what I want better than I do! I mean that literally . . . not sarcastic. Thanks for the help!

I have another question that perhaps belongs in another thread. I have two date fields: DateIn and DateOut. In addition, I have a Yes/No field. Can I disable DateIn or DateOut input based on the Yes/No field entry? For instance, "Yes" would disable input to DateIn and enable input to DateOut. "No" would do the reverese.

 

[Eupher]

Certainly. I'll assume that when you say "Yes/No" field, you mean a Yes/No field in your table bound to a checkbox on your form. In the form's On Current event, create a procedure with code that looks like this:

Me![DateIn].Enabled = Not Me!chkMyCheckbox
Me![DateOut].Enabled = Me!chkMyCheckbox

Then put the same code in an event procedure for the After Update event of the checkbox.

You'll also probably want to set a default value for your Yes/No field or you'll get an error message when moving to a new record or a record where no value has been assigned to the field. If you don't want to set a default value you can still use the same basic idea, it just involves using some If..Then statements in your code to test for a true or false value in the field.

HTH...

Ken S.