Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    table entry form (2003)

    I currently use a form to enter data received from the previous day's production report (completed by people in the field). This is very time consuming, b/c a single production report contains multiple pieces of equipment that are tracked in my db. What I am looking to do is create a form that looks like the production report that I can use to easily enter the data. My table has the following fields: Date, Job Number, Crew, Equip, Code, Hours. The form I currently use for one day of work I have to enter the Date, Job Number, Crew for every different Equip on a single day. So I am wanting to have a form that I enter the Date, Job Number, Crew only once and then have all the Equip listed. Then I would fill out the info and the table would fill in accordingly. How do I go about this?

    thanks
    Jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table entry form (2003)

    I would split the table into two tables:

    tblMain:
    ID (primary key, autonumber), Date, Job Number, Crew

    tblSub:
    ID (long integer, linked to ID in tblMain), Equip, Code, Hours
    Composite primary key on ID and Equip.

    tblMain would be displayed in a main form, and tblSub in continuous subform.

    I assume that you have a table, say tblEquip, listing all different Equip. You can put a command button on the main form to generate records for the subform:

    Private Sub cmdCreateSubrecords_Click()
    ' Test if there is a record.
    If IsNull(Me.ID) Then
    MsgBox "Create a main record first.", vbInformation
    Exit Sub
    End If

    ' Test if subrecords already exist
    If DCount("*", "tblSub", "ID=" & Me.ID) > 0 Then
    MsgBox "Subrecords already created", vbInformation
    Exit Sub
    End If

    ' Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Insert records into subtable
    strSQL = "INSERT INTO tblSub (ID, Equip) SELECT " & Me.ID & _
    " AS ID, Equip FROM tblEquip"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    ' Requery subform
    Me.sbfSub.Requery
    End Sub

    Here, sbfSub is the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: table entry form (2003)

    Sorry for the delay in being able to reply. I have done as indiacted, but in the code you provided, that requeries the sub form I am getting a Compile Error on teh last part: <pre>Me.sbfSub.Requery
    End Sub</pre>


    It does not like the: <pre>.sbfSub</pre>


    Can you tell me what not?

    Thanks,
    jackal

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: table entry form (2003)

    Is sbfSub the name of the subform control on your form? That's the name of the control, not necessarily the name of the subform in that control. If your control is names something else, like Child1 for instance, you need to substitute the actual name of the control for sbfSub.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: table entry form (2003)

    Thanks to both of you, this will prove to save much time in data entry.

    Jackal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •