By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Button Click Automatically Adds Record w/ Default Values

P: 10
I have a database that tracks patient orders and order progress.

On one single-view form, frmOrderUpdate, the users are entering information into text box fields named PreCertNo and Expiration, and each record is associated with an OrderID.

I have a second form (split view/datasheet), frmOrderProgress, which will open behind the scenes, with a default value for its OrderID field set to be that of the current record open on frmOrderUpdate. A second text box field on frmOrderProgress would have a default value that combines the PreCertNoand Expirationfrom frmOrderUpdate, along with a couple of other fields that track the timestamp and Windows user ID.

My goal is to have the user click a button on frmOrderUpdate once the PreCert and Expiration values have been entered, and for a record to be automatically added on frmOrderProgress with the appropriate default values.

Disclaimer: I am self-taught with VBA as it pertains to Access, so please excuse the lack of sophistication with my description. I'm happy to provide more detail as requested.
Mar 27 '17 #1

✓ answered by NeoPa

My approach would be to build the first form on a recordset that has what you want to add. Any number of the controls can be (but need not be if your requirements are that they're visible) invisible on the form. Each control can be set up with a .DefaultValue property that ensures a value is set when the record is written in spite of no direct operator input. Any value you need to set programatically can be done easily enough.

The button to write the record can simply move to the next record in the recordset and the original is written away automatically.

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
I have a feeling you are using the second Form "frmOrderProgress" to perform your Insert of a new record. Unless there is some magical validation code or other equally magical function being performed on the Form, you shouldn't need a form to insert a record into the Database. You could write some code similar to the following and attach it to the button on frmOrderUpdate:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. sSQL = sSQL & "INSERT INTO [tblFormProgress] ("
  3. sSQL = sSQL & "  OrderID "
  4. sSQL = sSQL & ", PreCert"
  5. sSQL = sSQL & ", Expiration "
  6. sSQL = sSQL & ") VALUES ("
  7. sSQL = sSQL & " " & Me!OrderID & ""
  8. sSQL = sSQL & ", '" & Me!PreCert & "'"
  9. sSQL = sSQL & ", '" & Me!Expiration & "'"
  10. sSQL = sSQL & ")"
  11. CurrentDB.Execute sSQL
Mar 28 '17 #2

NeoPa
Expert Mod 15k+
P: 31,419
My approach would be to build the first form on a recordset that has what you want to add. Any number of the controls can be (but need not be if your requirements are that they're visible) invisible on the form. Each control can be set up with a .DefaultValue property that ensures a value is set when the record is written in spite of no direct operator input. Any value you need to set programatically can be done easily enough.

The button to write the record can simply move to the next record in the recordset and the original is written away automatically.
Apr 2 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.