Loading Data Into a Form (1 Viewer)

[newbieVS]

I've got a form connected to a table. Under properties, Data tab, I've set Data Entry to "Yes". This is good because it default the form to a new record when I open it. However, I would also like to have the field in the form that is a primary key to be a drop down menu where I can load existing records into the form for modification.

How do I do that?

 

[isladogs]

Do a forum search for Search form. Lots of examples.
Or there's a good example here http://allenbrowne.com/ser-62.html

Welcome to the forum as well

 

[newbieVS]

Thanks ridders. I've created a listbox that loads in primary key (using wizard). However, when I select one of the values in the listbox, it doesn't populate the rest of the forms with the values in the field.

How can I get all the other fields in the form to be populated once I select the primary key value from my listbox?

 

[isladogs]

You need the after update event for the listbox
What code do you have now?

 

[newbieVS]

Ah I see. I did not have an after update event. The only code I had was to load existing primary keys into the list box. Code was simply what was generated by wizards:

Select [primarykey] from [tablename]

What code should I put into the after update event?

 

[Gasman]

I obviously do not know your system, however do you really want to add a new record every time the form is opened?
I have created various systems with my little knowledge of Access and 99.99% of the time, I am opening a form to edit something.?, I try and use as many Access built in features as possible as I am a novice.
When I want to add I just use the * at the bottom of the form or have code that adds a blank record ready for data entry. You could supply button if needed.

Perhaps worth a thought?

 

[newbieVS]

I agree I don't necessarily want to add a new record everytime I open the form. Basically, what i want to do is that when the form opens up, it doesn't pre-populate the fields. I achieved this by setting Data Entry to "Yes" in properties. Now I've added a listbox on the form that lists the existing primary keys from the table. Once one of the primary keys are selected, I would like all the other fields in the form to be populated using the values for that record. The idea is that once its loaded, a user can edit existing records using the same form.

 

[isladogs]

If you are using a bound form, the field controls will automatically be populated as you move to that record

 

[newbieVS]

I'm trying to bound my form. It seems I need to set ControlSource. I can find RecordSource but can't find ControlSource. I guess more generally I'm trying to see how I can bound my form to my table?

 

[isladogs]

You set the record source for the form.
Normally this is done when the form is created but it can be done/edited later if necessary

Controls like textboxes have a control source which is the field to which the control is bound
Listboxes / comboboxes can have both a control source (as above) and a row source ...from which a selection is made

TBH I suggest you start again with a new bound form using the wizard & let Access do the hard work for you

 

[mike60smart]

Hi

As Colin has suggested create a New Form using the wizard which allows you to select the table to which it will be bound.

You can add a Combobox in the Header of the Form which will allow you to search for a specific Record.

 

[newbieVS]

yep. I'm going to try that and also write the code required to load data. thanks all.

 

[missinglinq]

You cannot retrieve existing Records in a Form where Data Entry is set to Yes; the existing Records aren't part of the RecordSource.

This kind of thing can be done by setting Data Entry to No, and moving to a New Record when opening the Form:

Private Sub Form_Load()
  DoCmd.GoToRecord , , acNewRec
End Sub

Linq ;0)>

 

[Pat Hartman]

This is the method I use.
1. Data Entry is set to No because I want to use the form for both add and search
2. There is one or more combos or search boxes in the form's header. If there is one combo or box, the AfterUpdate event of the control requeries the form. If there are multiple search boxes, I generally add a search button.
3. The RecordSource query of the form references the combos in the form header --
Where CustID = Forms!frmCustomer!cboSearchCustID

Using this method the form opens blank. The user can then enter a new record or use the combo to find the record he wants.