Solved Autofilled data in a form from another form (1 Viewer)

[Pettor]

Hi guys, I would some help with the below.

I have a form, in which, the user enters some data and updates a table. This form opens through a button from a previous form where some specific data are retrieved and presented from a different table.

What I would like to do, is the empty form to get into some fields data from the previous form, by default, and then the user to fill in the rest by hand. After that, the whole record needs to be stored in a different table.

Since the form that needs to update the new table has a "recordset source" linked with the table that finally needs to get updated, I can not bring the data I want from the previous form that has the details that need to be autofilled.

So, to make the example realistic, let's say that I have a table with a list of houses and their addresses that is presented in a form.

I select one of them, and through a button, a new form opens with the data of a specific house.

Then I want a new button inside this last form to open a third form where some data will be auto-filled (let's say the address of the home) and the user will enter some additional info, with data irrelevant to the initial table, where the address is stored.

In the end, I want all of the data from the last form to be stored in a new table. Any opinions?

Thanks

 

[ebs17]

Duplicate data by copying is not a good idea and violates normalization rules. The address is already in a table and can be identified via an ID. This ID is the reference to this address.
In order to use the address, only this ID would have to be transferred as a foreign key to the new form (via OpenArgs) or to another table.

 

[Pettor]

Hi Ebs,
I don't want to enter again the address, I just need it to appear in the new form as the user needs to see this detail before entering the additional data. The entries have an ID but again, I don't know how to do it and I am totally naive in Access... Thanks!

 

[Gasman]

You would only store the ID of the address (if it was actually needed), not the address itself.

You can either pass the ID in when you open the form via OpenArgs. Then get the data into unbound controls, as you do NOT want to replcicate the actual address.
You can set a Tempvar and then use that in whatever form you want to get your data. Could use a DlookUp() or a recordset depending on how many items you want to get?

 

[ebs17]

One can display the address in a form provided by the ID. The additional entries for the further table could be made via a subform that is linked to the main form via this ID. This is simple and hassle-free access automation.

To do this, however, you have to talk about the data model first, to display it via the relationship window. Database creation always starts with a good planning of the tables and their structures.

 

[Pettor]

One can display the address in a form provided by the ID. The additional entries for the further table could be made via a subform that is linked to the main form via this ID. This is simple and hassle-free access automation.

To do this, however, you have to talk about the data model first, to display it via the relationship window. Database creation always starts with a good planning of the tables and their structures.

This is what I thought in the first place, but I don't really know how to do it.

The button that opens the new form has this code:

Private Sub Command17126_Click()
DoCmd.OpenForm "List"
DoCmd.GoToRecord acDataForm, "List", acNewRec
End Sub

Then the form "List" starts completely empty.

How could I bring the data from the previous form (or table maybe) into this form and how the subform can be linked to the main form?
I understand that using as a "Recordset source" the last table, will store the information but I don't know how to make the connections.

Sorry for being so naive, but it is the first project that I am trying to accomplish...

Thanks

 

[Pettor]

You would only store the ID of the address (if it was actually needed), not the address itself.

You can either pass the ID in when you open the form via OpenArgs. Then get the data into unbound controls, as you do NOT want to replcicate the actual address.
You can set a Tempvar and then use that in whatever form you want to get your data. Could use a DlookUp() or a recordset depending on how many items you want to get?

Thank you so much, but I do not know how to do it. Any code example?

 

[arnelgp]

you may try:

Private Sub Command17126_Click()
DoCmd.OpenForm "List"
DoCmd.GoToRecord acDataForm, "List", acNewRec
With [Forms]![List]
    'FirstName and LastName are Textbox name in List Form
    !FirstName = [Forms]![TheFormNameThatHasData]![FirstName]
    !LastName =[Forms]![TheFormNameThatHasData]![LastName]
    ' etc...
End With
End Sub

 

[Pettor]

you may try:

Private Sub Command17126_Click()
DoCmd.OpenForm "List"
DoCmd.GoToRecord acDataForm, "List", acNewRec
With [Forms]![List]
    'FirstName and LastName are Textbox name in List Form
    !FirstName = [Forms]![TheFormNameThatHasData]![FirstName]
    !LastName =[Forms]![TheFormNameThatHasData]![LastName]
    ' etc...
End With
End Sub

That was it! Thank you very much!! Knowledge is power!!!