Using a Combo-Box with Data Entry Form

[jtmm]

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

Private Sub GenerateRecords_Click()
Dim mn As Long
Dim Rec As Long

Rec = (Me.Text2 - Text1) + 1

For mn = 1 To CInt(Rec)
  DoCmd.GoToRecord , , acNewRec
  Me.VoucherNo = Me.AutoDate + mn
 
Next mn

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.

[GKJR]

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?

[jtmm]

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

[GKJR]

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.

Dim db As Database
    Set db = CurrentDb
Dim rst As Recordset
    Set rst = db.OpenRecordset("[B]Your Query Name Here[/B]", dbOpenDynaset)
    
With rst
    .MoveFirst
    Do While Not .EOF
        .Edit
        ![B]FieldToBeEdited[/B] = Me.[B]ComboBoxNameHere[/B]
        .Update
        .MoveNext
    Loop
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).

[zmbd]

jtmm:

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

There are several links here:
---

• MS Access 2010 Tutorials
• Access Basics - By Crystal
• Micrsoft: Office2010 tutorials
• Microsoft: Getting started with Access 2010
• > Database Normalization and Table Structures.
• Nassi-Shneiderman Charts

---
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.