G
Guest
I have a database that records daily production data for each shift and each
line. At the switchboard screen, the user will enter date, shift, and prod
line, and with a click of a button the form will open with the filtered data
displayed and default values set based on those user entries (date, shift,
line). The user will then create numerous production records for that
particular date, shift and line.
The problem that I have is at the beginning of the shift when there is no
record stored yet.
Below is the VBA coding that I have:
If Shift_Selection_Field <> "" And Line_Selection_Field <> "" Then
StrSql = ""
StrSql = "SELECT * FROM tbl_main WHERE"
StrSql = StrSql & " Prod_Date = #" & Date_Selection_Field.Value & "# AND
Shift = " & Shift_Selection_Field.Value _
& " AND Line =""" & Line_Selection_Field.Value & """"
StrSql = StrSql & " ORDER BY Tbl_Main.Prog_Num"
DoCmd.OpenForm "frm_main_Data_Entry", , StrSql
DoCmd.GoToRecord , , acLast
With [Forms]![frm_main_Data_Entry].[Prod_Date_Field]
.DefaultValue = "#" & [Date_Selection_Field].Value & "#"
.Locked = True
End With
With [Forms]![frm_main_Data_Entry].[Shift_Field]
.DefaultValue = [Shift_Selection_Field].Value
.Locked = True
End With
With [Forms]![frm_main_Data_Entry].[Line_Field]
.DefaultValue = """" & [Line_Selection_Field].Value & """"
.Locked = True
End With
Else
MsgBox "Please verify [DATE] field, [LINE] field, and [SHIFT] field are
filled in!", vbExclamation
End If
The problem is that at the beginning of the shift (when there is no records
to start with), the DoCmd.GoToRecord , , acLast creates error that said can't
go to specified record. The other problem also is that Access does not
prepare the form for new record data entry, it is basically just showing
blank page. So there is no way to even start recording new records.
Is there any way to fix this problem? Do I use the wrong approach in
progamming this form to achieve what I want to do? If there is a better way
to program this please let me know. Your help is greatly appreciated. Thanks.
I am using Windows XP, Access 2003.
Baron
line. At the switchboard screen, the user will enter date, shift, and prod
line, and with a click of a button the form will open with the filtered data
displayed and default values set based on those user entries (date, shift,
line). The user will then create numerous production records for that
particular date, shift and line.
The problem that I have is at the beginning of the shift when there is no
record stored yet.
Below is the VBA coding that I have:
If Shift_Selection_Field <> "" And Line_Selection_Field <> "" Then
StrSql = ""
StrSql = "SELECT * FROM tbl_main WHERE"
StrSql = StrSql & " Prod_Date = #" & Date_Selection_Field.Value & "# AND
Shift = " & Shift_Selection_Field.Value _
& " AND Line =""" & Line_Selection_Field.Value & """"
StrSql = StrSql & " ORDER BY Tbl_Main.Prog_Num"
DoCmd.OpenForm "frm_main_Data_Entry", , StrSql
DoCmd.GoToRecord , , acLast
With [Forms]![frm_main_Data_Entry].[Prod_Date_Field]
.DefaultValue = "#" & [Date_Selection_Field].Value & "#"
.Locked = True
End With
With [Forms]![frm_main_Data_Entry].[Shift_Field]
.DefaultValue = [Shift_Selection_Field].Value
.Locked = True
End With
With [Forms]![frm_main_Data_Entry].[Line_Field]
.DefaultValue = """" & [Line_Selection_Field].Value & """"
.Locked = True
End With
Else
MsgBox "Please verify [DATE] field, [LINE] field, and [SHIFT] field are
filled in!", vbExclamation
End If
The problem is that at the beginning of the shift (when there is no records
to start with), the DoCmd.GoToRecord , , acLast creates error that said can't
go to specified record. The other problem also is that Access does not
prepare the form for new record data entry, it is basically just showing
blank page. So there is no way to even start recording new records.
Is there any way to fix this problem? Do I use the wrong approach in
progamming this form to achieve what I want to do? If there is a better way
to program this please let me know. Your help is greatly appreciated. Thanks.
I am using Windows XP, Access 2003.
Baron