Pre-populating a form based on one table

[tonycl69]

Hi all, I have looked around and spent a few days trying to get my head round this one.
I have 2 tables, one of the tables contains data that does not change and does not need editing, the table 2 holds recorded data that can be edited or new records created. What I would like is to open a form, select, from a combo, the primary data from table 1 to populate set fields that are not editiable, but still alow the user to enter new data into the rest of the form and therefore create a new record containing both table 1 and table 2 new data, I do hope this makes sense.

Cheers

Tony

[ranman256]

after picking the combo,it will run an append qry.
the combo can be tied to a table if you need to post different data where it has the combo pick / appd query
pick1, qaQry1
pick2, qaQry2

set the combo to 2 columns, widths 1;0, to hide the query column.
user picks the item in the 1st column then it runs the 2nd column

sub cmbo_afterupdate()
   docmd.setwarnings false
   docmd.openquery cmbo.column(1)     'in code, the columns start at zero , so column 2 is 1 
   docmd.setwarnings true

   vRec = dmax("[dataID]","table")
   me.filter = "[dataID]=" & vRec
   me.filteron = true
end sub

once the data is appended , grab the max ID created (or other criteria) and load the 1 record for the user.

[tonycl69]

Thanks for you response. Table 1 is like a lookup, nothing will be edited. What is the forms underlying table then?

[ranman256]

the data you are editing is in the form
the combo will append a new record, prepopulated, then filter on it.

[tonycl69]

Once I get my head round it I will give it a go. Does it matter how many columns there are in the table I want to use to populate the form? There are over 120 columns in the one table and about the same in the other.

[tonycl69]

When I create the append query, what is the table I am using for that query is it table 1 or table 2. Also how are the fields populated in the form?

[tonycl69]

I am getting an 'object required on this line 'DoCmd.OpenQuery query1.Column(1) 'in code, the columns start at zero , so column 2 is 1'

[ranman256]

try
vQry =combo.Column(1)
'DoCmd.OpenQuery vQry

[ssanfu]

What I would like is to open a form, select, from a combo, the primary data from table 1 to populate set fields that are not editiable, but still alow the user to enter new data into the rest of the form and therefore create a new record containing both table 1 and table 2 new data, I do hope this makes sense.

Doesn't really make sense. Why are you duplicating data?

Table1 Primary key field should/would be linked to Table2 foreign key field.
The form would have Table1 as the record source. There would be a sub form with Table2 as the record source. No need for any code.......

[tonycl69]

Yes I know that I have done that several times in the past with other databases.however, I do not want tables to be updated, table 2 is the tables I want updating, and tables 1 is used to populate certain fields in a form and table 2 to be updated with a new record that contains the pk from table one for when I need to print a report. There is a fk in table 2 linked to table 1 pk. Table 1 is a one to many in table 2 but there are over 1000 records in table 1.

[ssanfu]

Can make the controls on the main form non-editable.
Can use an unbound combo box to select the main form record; then add/edit the data in the sub form.

Just saying, you are making this harder than it needs to be.....

Good luck with your project....

[tonycl69]

It does help to talk about it and the more I talk about it the clearer it is becoming

[ssanfu]

I totally agree!