forms & sub-forms

[amypaulsen]

I'm very new at access, I know VB & Excel, but access...ugh... I have built a form and I want the user to put a number in one field ex: W48, the account number in another field ex: 43815 and based upon that information, pull up the account from the table with each category attached to that account ex: Sales, Product cost, Labor, etc (8 rows) and in the corresponding week, I want the user to enter their data into the form and then be able to request the next account...for like 200 accounts. I am using a subform because the user wants to see an excel sheet-like view...so...the acct number, the 8 categories and be able to enter the corresponding number row after row...and then have it go into the table. Can you help? I've read my access book until I'm purple and can't DO IT!!!

 

[fly_champ]

If you're familiar with vb...
try using a recordset
I use it in a form, but with it I have no need for subforms.

to find recordset commands, look for RS.[something]

Private Sub DoneBtn_Click()
Dim db As dao.Database
Dim rs As dao.Recordset
.............
some code omitted 
.............
SkipMe:
' if there are no screw ups, enter data into table
If reenter = False Then
' if all fields in row one are present, add input
    If Me.CboName.Value > 0 And Me.CboPart1.Value > 0 And Me.Hour1.Value > 0 Then
        rs.AddNew
            rs!Name = CboName.Column(0)
            rs!UserID = CboName.Column(2)
            rs!Date = DateBox.Value
            rs!Parts = CboPart1.Column(2)
            rs!PartID = CboPart1.Column(0)
            rs!Time = Hour1.Value
        rs.Update
    End If
Set rs = Nothing
Set db = Nothing

this code takes the values from one row of comboboxes (there are more, but omitted for clarity) and enters them all at once as one entry into a db with fields of name,userid,date etc.

a recordset is useful since you can move around with it like this code to eliminate extra rows by adding their data to existing rows:

Private Sub Form_Load()

'stop annoying prompts
DoCmd.SetWarnings False
      
Dim db As dao.Database
Dim rs As dao.Recordset
Dim sSql As String
Dim column1 As String
Dim column2 As Double
Dim column3 As Integer
Dim column4 As Double

Set db = CurrentDb()

Set rs = db.OpenRecordset("tbl_person_time", dbOpenDynaset)

If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst 'capture the first row data
column1 = rs!FullName
column2 = rs!sumoftime
column3 = rs!UserID
column4 = rs!hourtime
rs.MoveNext

'compare previous row data with current row data
Do Until rs.EOF
    If column3 = rs!UserID Then
        ' put current info into variables
        'MsgBox rs!FullName
        column4 = column4 + rs!hourtime
        column2 = column2 + rs!sumoftime
        'MsgBox column2
        'MsgBox column4
        rs.Delete 'current row leaving modified row
        rs.MovePrevious
        rs.Edit 'previous row
            rs!FullName = column1
            rs!sumoftime = column2
            rs!hourtime = column4
            'rs!timein = 0
        rs.Update
    Else
        rs.Edit
            rs!timein = 0
        rs.Update
    'reset row values
        column1 = rs!FullName
        column2 = rs!sumoftime
        column3 = rs!UserID
        column4 = rs!hourtime
    End If
    rs.MoveNext
Loop
End If

Set rst = Nothing
Set db = Nothing

'allow annoying prompts
DoCmd.SetWarnings True
    
 End Sub

hope this helps

Jon