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

Using a Combo-Box with Data Entry Form

P: 4
I have a data entry form that uses a control button to process multiple "samples" using

Expand|Select|Wrap|Line Numbers
  1. Private Sub GenerateRecords_Click()
  2. Dim mn As Long
  3. Dim Rec As Long
  4.  
  5. Rec = (Me.Text2 - Text1) + 1
  6.  
  7. For mn = 1 To CInt(Rec)
  8.   DoCmd.GoToRecord , , acNewRec
  9.   Me.VoucherNo = Me.AutoDate + mn
  10.  
  11. Next mn
  12.  
  13. End Sub
In the form I have a Combo Box that selects a field value I would like applied to all the multiple entries. Currently only the first entry retains the combo box information.
Mar 10 '14 #1
Share this Question
Share on Google+
4 Replies


100+
P: 104
Is this combo box in the Form Header/Footer, or is it in the Detail Section?
You say this is a data entry form, does that mean you want to apply a similar value to a field in all of the new records in the instance that the form is used?
Mar 10 '14 #2

P: 4
Is this combo box in the Form Header/Footer, or is it in the Detail Section? Have tried the ComboBox in both sections with the same results


You say this is a data entry form, does that mean you want to apply a similar value to a field in all of the new records in the instance that the form is used? Yes - When we create new samples we want the ComboBox value applied to all of them.

Thanks
Mar 10 '14 #3

100+
P: 104
The problem you're having is because you can't use a control to represent the same field in multiple records. Controls on a bound form represent a single field in a single record. This is the reason you had the correct value in only the first record when you had the control in the header section.

As far as I know you will need to create a saved qry or SQL string that outputs all of the new records on your continuous data entry form. Even in a data entry form, each time you move into a new record, the previous record is committed to the table or record source it is based on. I can think of one way to do this, but it might not be perfect for your application. If you filter your query by Null values in the field to be updated automatically you would capture all of the relevant records, but you may also have other records in that table that have a Null value.

Once you have your query that captures only the records you want, try using the following. Make sure you change the values that I have in bold. It should be placed in the "On-Click" event of your button.

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2.     Set db = CurrentDb
  3. Dim rst As Recordset
  4.     Set rst = db.OpenRecordset("Your Query Name Here", dbOpenDynaset)
  5.  
  6. With rst
  7.     .MoveFirst
  8.     Do While Not .EOF
  9.         .Edit
  10.         !FieldToBeEdited = Me.ComboBoxNameHere
  11.         .Update
  12.         .MoveNext
  13.     Loop
  14. End With
If you filter by using the null values like I mentioned above, your query won't return any records after you click the button until you enter more records (presuming you selected a value in the combo-box).
Mar 10 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
jtmm:

Please review the following tutorials and concepts, especially the concept of normalization:

There are several links here:
------
Scan the titles here, there's everything from the very basic to the esoteric: Microsoft Access / VBA Insights Sitemap

Same with this site: Allen Browne's Access Tips

And when you get into subforms:
This is the most simple and sweet: Allen Browne: Referring to Controls on a Subform

THis is the reference I will use when I get lost:
Forms: Refer to Form and Subform properties and controls
I'm worried enough that this site might ever go down that I took advantage of the download offer of a hardcopy of the table - upper right hand corner!
--------------------------------

Once you have these basics for database and form design you'll find things much easier to accomplish.
Mar 11 '14 #5

Post your reply

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