Fill Main form fields from subform record selector

[jeffjohnson29]

I have a form with a subform in it that keyword searches for records in a table that tracks assets. What I would like to do is be able to double click on the record selector in the subform and have it open the record/fill in the controls in the main form.

I know I need to set the event on the subform to [event procedure] and I am pretty sure I need a docmd in the vbs for the dblclick event and I am sure I need the set.focus command to the parent form but I do not know exactly how to code this out. I can attach a sample database if it is requested.

 

[Ranman256]

double-click may not be the best event. I use a button with a hot key
(put an ampersand in front of the letter in the caption to make it a hot key: &Open)


select item in subform, click Open button,
button click event:

If the button is in the subform:
docmd.Openform "frmDetail",,,"[key]=" & me.txtID

If the button is in the parent form:
docmd.Openform "frmDetail",,,"[key]=" & me.subform.form.txtID

 

[jeffjohnson29]

double-click may not be the best event. I use a button with a hot key
(put an ampersand in front of the letter in the caption to make it a hot key: &Open)


select item in subform, click Open button,
button click event:

If the button is in the subform:
docmd.Openform "frmDetail",,,"[key]=" & me.txtID

If the button is in the parent form:
docmd.Openform "frmDetail",,,"[key]=" & me.subform.form.txtID

I don't necessarily want another button to fill fields in a form, I don't think it would look all that good. I assume I could still use the code except for button on_click i would change the sub to form_dblclick. the form is already open so I am unsure if the docmd.openform is correct to use this way. Since I am new to VBS I don't exactly know what details in the code to change to specific things on my database, i.e. "frmdetail" "[key]=" I attached a sample of my database in case I am not explaining what I am wanting to do well.

 

[Ranman256]

yes, you open forms with the command , openform.
this form would show the detail of the record you want opened.

(i cannot download)

 

[Pat Hartman]

You already have the parent record open in the main form. Opening a second form with the same record although possible, isn't recommended. What happens if the main form record is dirty? Are you prepared to deal with the confusing three-option error message and do the correct thing.

Also, if you have to do this, your schema is not normalized. The only data field in your subform that will also appear in the main form is the Foreign Key. It is the FK that links to the PK of the parent record that connects the two tables. The reason for having child tables is that you have 1-m relationships. How can the many update the one? It simply doesn't make sense.

 

[jeffjohnson29]

You already have the parent record open in the main form. Opening a second form with the same record although possible, isn't recommended. What happens if the main form record is dirty? Are you prepared to deal with the confusing three-option error message and do the correct thing.

Also, if you have to do this, your schema is not normalized. The only data field in your subform that will also appear in the main form is the Foreign Key. It is the FK that links to the PK of the parent record that connects the two tables. The reason for having child tables is that you have 1-m relationships. How can the many update the one? It simply doesn't make sense.

For my case the subform is essentially a keyword search that will list records with limited details that meet the search criteria. In the search subform the records are set to not be able to be changed and in the main form you have to click save before the record is updated. hopefully this should address any dirty record problem. As for the Primary Key and the Foreign Key statement and the 1 to many statement I am uneducated and do not know what you mean there. I could be misunderstanding how the relationship between tables and forms work. Would it work better if my search button/subform was a query instead of how I have it?