Display table in form, with linked record ID between form and table

[dlambert]

Hello All,
I am trying to do something that i would have thought would be simple, but have been turning around around in circles not really getting anywhere, so time for forum question!

I want to have a form which is to be used mainly to add new records but also want to be able to modify previously entered records.
Below the data entry fields (textboxes, comboboxes) i would like to have a subform displaying the same table that is being added/modified by the main form. (up to here all is OK for me)
But i would like that if i select a record in the table, the main form directly goes to display that record in in the entry fields, so i can then modify them as i want before saving the record.
So i guess i would have to link the record ID between the main form and subform? (i just cannot find out how to do this)
Any guidance is much appreciated.

 

[bob fitz]

Set both the "Link Master Fields" and the "Link Child Fields" properties of the subform control to the name of the Primary Key field of the table.

 

[dlambert]

Set both the "Link Master Fields" and the "Link Child Fields" properties of the subform control to the name of the Primary Key field of the table.

Thanks for your response bob!
I tried that, and when i do the table only displays the one record that matches the ID from the main form (the other records are not displayed)...
I want the entire table to be displayed in the subform, but that the selected record in the subform matches the selected record in the main form...

 

[cheekybuddha]

Hi, what version of Access are you using?

Though they are often troublesome in other ways, your description of what you want sounds like a 'Split Form' is what you need.

You should be able to select this from the Form wizard

 

[Gasman]

A split form would do that for you.?

 

[dlambert]

Yes, i think that i will have to go with a split form. My only problem with that is that the columns in the table are directly linked with the fields that are in the main form, but for me i only want to be able to input some of the fields, with the other going to their default values.
If i delete the fields i do not want to edit from the main form, they also get deleted from the subform below. So i think i will have to keep them in the main form but just put them tiny in a corned and hidden, hopefully that will work.

 

[bob fitz]

I agree with the comment by others that a split form would do what you want but alternatively you could try the following code in the main forms On Current event:

Me.SubformName.SetFocus

    DoCmd.SearchForRecord , "", acFirst, "[NameOfPK] = " & [NameOfPK]

You would need to replace with your SubformName and your NameOfPK

 

[Gasman]

Yes, i think that i will have to go with a split form. My only problem with that is that the columns in the table are directly linked with the fields that are in the main form, but for me i only want to be able to input some of the fields, with the other going to their default values.
If i delete the fields i do not want to edit from the main form, they also get deleted from the subform below. So i think i will have to keep them in the main form but just put them tiny in a corned and hidden, hopefully that will work.

Perhaps check out the Emulated Split Form that has none of those problems. I use one to pretty much do what you are after.

 

[Deleted member 147267]

I'm confused between post 6 and the original. A split form is for 2 views of the same record source. If you activate a datasheet record, the header is synced to that record and becomes the current record. If you navigate to a header record that record in the datasheet becomes current. Thus it seems to satisfy the first post. However, on a split form there is no such thing as a main form/sub form in the usual sense. If you look at it in design view, it looks like a normal form with a header. If you want a main record along with a view of a set of related records, then a split form is not the answer. Thus the confusion.

 

[cheekybuddha]

@Micron, I'm a bit confused by your post. Did I miss the mention of related records?

per OP:

i would like to have a subform displaying the same table that is being added/modified by the main form.

 

[Deleted member 147267]

[QUOTE="cheekybuddha, post: 1695338, member: 133070"
I'm a bit confused by your post. Did I miss the mention of related records?
[/QUOTE]
No, there was no mention, only an inference:

I want the entire table to be displayed in the subform, but that the selected record in the subform matches the selected record in the main form...

Subform / main form implies a one to many relationship. I said that because there is no subform on a split form. So what does "matches" mean? It matches because it's related and it's on a sub form or it matches because it's the same record at the top and bottom of the same form?

 

[cheekybuddha]

I think in this instance that the subform is based on the same table as the main form and linked on the same pk field (I might well be wrong).

However, this setup will cause issues with 'another user has updated this record'

 

[Gasman]

My interpretation was the o/p wanted a datasheet/continuous form to select records, much as you would if looking directly in table, then have those fields in the 'main' form to be able to edit them.?

I use exactly that same feature in one of my forms, with the subform not allowing any edits, merely selection. I am using the emulated split form in it's simplest form to do that.