Greetings,

I am creating a form based on a table, and the form shows records from that table. I have two things I'd like to add to the form to modify it, yet I don't know how. First--what can I do which would allow the user to type in a particular identifier that is associated with a record and then have the fields from that record populate the form? For example, I would type JBG in the identifier field and all the particulars for "Joe's Bar and Grill" (full name, address, phone, manager, etc.) then show up in the remaining fields of the form.

Second, which may or may not be at all associated with my first wish, once I type JBG in the identifier field, what do I need to do to have a Yes/No box turn into Yes? In other words, toggle a field which indicates if a record has been viewed.

Any advice with the what--and definitely the how--would be appreciated. I don't know VBA, so explanations using commands from Access would probably be advisable. And, my Access knowledge is relatively limited, so be patient if I ask for clarification.

Thanks in advance.
Ken

Hi Ken,

The textbox where you need to type in the identifier has to be "unbound." Otherwise, anything you type in it will just update the current record.

Try adding a Combobox on your form using the Wizard and select the third option: "Find records..."

As for updating a Yes/No box, you can add code in the form's Current event to check the box. Something like:

Me.CheckboxName = True

Just my 2 cents...

Hi, Mr DBguy,

Assuming I did it properly (big assumption), I created a combo box, selected the third option per your suggestion, then selected ONLY the field with the identifier for the box. I then went out of design view, typed a valid identifier in the combo box, hit tab, and nothing happened except my cursor moved to the next field in the form. Obviously I did something wrong. Any thoughts?

As for updating a Yes/No box, you said to add code in the form's Current event. I assume that is different than what one finds looking in properties for a text box; how does one find the form's Current event?

Hope you're not frustrated yet!

Ken

Hi Ken,

When you first open the form, it should display some record, correct? Now, when you enter an identifier in the combobox for a record that is not being displayed, doesn't the form change the display to show the matching record for that identifier?

We should just work on this one for now.

Just my 2 cents...

My error--I was using a different form based on a query. I went to the form based on the table, and it worked the way you said it would. Thanks for the gentle correction.

Ken

Hi Ken,

Glad to hear you got it to work. Is that going to be good enough for you? If so, we can move on to the Yes/No question.

To find the Form's Current Event, go to the forms' Design View and in the Properties Window, click on the Event tab.

Look for the event called "On Current" and then select [Event Procedure] from the dropdown. If you don't see the Current event, make sure that "Form" is showing in the dropdown on the top of the Properties window.

Then, click on the three dots (...) to the right of the event to open the Code Builder, then type the following:

Me.CheckboxName = True

You'll have to replace "CheckboxName" with the actual name of your Yes/No box.

Just my 2 cents...

That did it. Thanks for your help on both items.

Ken

Do you need to know only if a record has been viewed since it was created or do you want to know when it was viewed and by who. IMHO just knowing that a record was viewed is almost worthless. Data is meant to be viewed so most records should be marked as viewed. But knowing when it was viewed and by whom would seem much more valuable.

Hi Ken,




Glad to be of help. I agree with Scott's point. Please consider what he is saying.

Good luck with your project.

Morning Mr DBguy,

I did a little playing around, and I noticed one item regarding the yes/no toggle that needs to be tweaked. When I open the form, the first record from the associated table is displayed, which is normal. Then, after I proceed to enter the identifiers for the businesses I'm looking at, going back and looking at the table shows that the first record which appeared in the form has a check, even though I didn't enter the identifier for that record. How can things be modified so that the yes/no toggles --only-- for the identifiers I enter, and not for the first record which automatically shows up?

Ken

If you are entering data into BOUND controls, you will be updating the currently displayed record.

Hi Ken,


One approach is to "not" show any records when the form first opens.

In the form's Open event, place this code:

DoCmd.GoToRecord , , acNewRec

Just my 2 cents...

I copied and pasted what you suggested, and that did give me a blank form when I opened it. However, and I'm not sure if this happened after this last modification or the one previous (as I didn't think to look for it), I am now getting blank records (new ones, not erasures of existing ones) in my table which are checked. How can those be prevented.

And, one other thing I noticed. When I entered the identifier and hit tab, the proper record shows up. Hitting tab again, though, moves to the next record instead of moving to the next field. That doesn't happen until the tab is used again, but in the wrong record, of course.

Seems like no simple project is simple.

Ken

Hi Ken,

In the matter of the form creating blank records, check if you have any code running that modifies the data, such as a default value, in your form. If you do, that automatically creates the new blank record.

In the matter of the tab key, make sure you set the tab order of the form properly. Make sure the first control on the form is also first in the tab order.

Just my 2 cents...

In regards to the tab issue, changing the tab order took care of that problem. Thanks.

I don't think I have any code running. The only thing I could think of was that in the table the form is based on there were four different fields that had 0 (zero) as a default, but I erased them in the design view and still get new, blank fields. Out of curiosity I removed the value from "on open", and the new, blank field problem disappeared. But then I was back to the situation of first record showing up when opening the form.

Don't know if this helps you any, but thought I'd pass it along. Have a good weekend--probably bother you again Monday.

Ken

Hi Ken,


What "value" did you remove? Did you mean the code?

Also, forgot to ask you, are there any code running in the form's Current event?

My Access terminology isn't that extensive, so I guess I did mean "the code" when I said I removed the value. I went to the form property sheet, highlighted and deleted "[Event Procedure]", which I'm assuming removed the code I had pasted there. And, for the OnCurrent question, what is there is the appropriately renamed "Me.CheckboxName = True", which was mentioned earlier in the thread -- wouldn't expect you to remember that due to the number of people you respond to throughout the day.

Ken

Hi Ken,

Thanks for your patience. Sorry, I did forget that we had worked on the Current event already, but I was also concerned if there were other codes already running in there besides the one we added earlier.

Removing [Event Procedure] from the property will not remove the code from the form, it will just stop it from running. Anyway, I'd like you to put it back and change the code in the Current event to this:

If Not Me.NewRecord Then
Me.CheckboxName = True
End If

Just my 2 cents...

Thanks. That looks like it's doing what I wanted. Appreciate your help.

Ken

Hi Ken,



Glad to hear you got it to work. Good luck with your project.