Main/subforms, unbound fields, writing to a table, HELP!

[jnfr]

Hi there,

I have a database that collects information about company operations either monthly or weekly based on several different elements.

For example:

On the main form (frmMaster), there is a Date and Week combo list. (these write to tblMaster)

On the subform (frmTest) there is something like this:

Subcat: _collect value from user_
Material: _collect value from user_
Data: _collect value from user_

They all write to the same table (tblSCData), so the fields are unbound.

The main form and subform are linked by a MasterID which is the primary key. With the unbound fields, I manually coded using SQL "Insert Into" to insert the subform elements into the table. Also, hidden in the main and sub form, I have a MasterID textbox which automatically displays the current MasterID of the form (i.e. when starting a new form, and selecting a new month and week, the primary key shows up in both hidden textboxes.)

I want to write the Month/Week/MasterID to tblMaster (no problem)
and Subcat, Material, Data, MasterID to tblSCData (problem)

This is my SQL Code for the latter

DoCmd.RunSQL "INSERT INTO tblSCData ( [Subcategory], Material, Data, MasterID ) values (Forms!frmMaster!frmTest.Form!txt1.Value, Forms!frmMaster!frmTest.Form!txt2.Value, Forms!frmMaster!frmTest.Form!txt3.Value, Me.MasterID.Value);"

I tried interchanging Me.MasterID.Value with Forms!frmMaster!frmTest.Form!MasterID.Value but to no avail.

When I point it to Me.MasterID.Value, it doesn't recognize it and instead asks me to manually punch in a value.

I may be missing something big here, anyone want to help out an Access newbie?

Thanks so much!

 

[Uncle Gizmo]

You don't need to write an SQL statement to do this, MS Access will update the data in the sub form for you. Have a look at this post here, in particular look at video 4 and video 5.

 

[jnfr]

But my fields are unbound, won't it make a difference?

Thanks!

 

[Uncle Gizmo]

If you set up things in the normal way with a record source for both forms, and bound controls then MS Access does all the work for you.

 

[jnfr]

I tried to do the bound controls, but I can't get it to work.

Basically I have:

Text box (locked, Subcategory name) Text box (edit-able, Data)
Text box (locked, Subcategory name) Text box (edit-able, Data)
Text box (locked, Subcategory name) Text box (edit-able, Data)
Text box (locked, Subcategory name) Text box (edit-able, Data)

They should all write to the same table. The subcat names are all different as should be the textbox inputs.

Problem now is that because they're all bound to the same field, when I change, say, the first text box, all the other text boxes change as well to reflect the new numbers.

So, is there something I am not getting? Probably, yes!

Thanks for your help.

 

[Uncle Gizmo]

Although the videos in the previous link I gave are not exactly relevant to your issue, they do provide quite a bit of information that I think will be useful to you. I would suggest you watch the videos from the beginning. One through five.