Populating a Table With Calculated Form Controls

[RunTime91]

Just about done with this project....Thank the Lord!!



Okay, I bound the controls of these forms (Main/Sub) to their respective tables; (Thanks all for that bit of wisdom) however, I have several controls which are auto-populated with DLookUps

Naturally, when I enter data into the bound controls that data is entered into the tables automatically; but, of course the controls which derive their data via a DLookUp

That data does not automatically get entered into the table

Thus, my question is what is the best way to populate a table with both bound and unbound controls

Would I use an INSERT Query for the controls not bound or is there a better way?

Thank You very much...

[CJ_London]

why do you want to store what is effectively a calculated or looked up value? just use the dlookup on your form, although there are often faster ways depending on the required output such as including the values in a query. But to answer your question, you would use an update query.

[June7]

Could simply use code behind form that populates field:

Me!fieldname = Me.controlName

The trick is figuring out what event to put code in.

But why save duplicate data? Why do you even need DLookup?

[RunTime91]

Ajax - June - As always - Thank you!

Your questions illuminate the obvious gap between what I know and how much I don't know (but trying like hell to learn)

I believe this bit of info might answer your questions about using calculated values - The owner of this project wants to use a flat table.

So, I'm using LookUps in the controlsource of as many controls as I can to speed up data entry, but once those LookUps autopopulate their respective controls I then need to add the values of those controls to what will be the main/flat table

And, of course this is just the main form of a main/sub form combination and the subform is completely bound...UGH!!!

Anyway,

Currently I'm looking at unbinding all of the controls and adding their values with VBA using code similar to below - This seems woefully inefficient -

Private Sub Command36_Click()
Dim Db As DAO.Database
'
Dim Rs As Recordset
'
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Select * From TblData")
'
Rs.AddNew
  Rs("Recipient") = Me.txtRecipient
    Rs("ReipientFaxNum") = Me.TxtFaxNum
      Rs("RequestDt") = Me.TxtRqstDate
        Rs("RequestPriority") = Me.TxtRqstPriority

'There are 43 fields in this table so this goes on for a bit - figured you get the gist with just this snippet of code.

Rs.Update
End Sub

Ajax - I'm not sure what you mean by, "just use the dlookup on your form..." I have placed these dlookups in the control source of the control - are you referring to something else?
Also, The desired output is to send a report directly to the printer

Thanks again you two for helping me out

[CJ_London]

No, access isn’t really intended for flat file data input so suspect you will struggle to create an effective app. Fine of course to use flat file output for reports and exports

Good luck with your project

[June7]

I think Ajax was saying to do the DLookup calc whenever the value is needed, not to save it.

Why does the user dictate data structure? Why should they care? As long as they can enter data and get desired output why should they even know what the data structure is?

Whether you use INSERT/UPDATE actions or recordset or my example, the real trick is still figuring out what event to put code into. You are using a button Click event which may be adequate. What if user clicks button again on the same data? Will a duplicate record be created?