key date and shift

G

Guest

Hi
I have designed 2 forms for user input one for morning shift one for late
shift they input Date noofemployee notemps etc each form has a field called
shift with default value of "M" or "L" .after data is entered user clicks
command button to accept entries which writes data into employee table.
My problem is as date is same data is overwritten .How can I get 2 rows for
each date ??
hope this makes sense
Thanks
Tina
code as follows
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Me.ProductionDate = Me.Text1
Me.Shift = "M"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Me.ProductionDate = Me.Text1
Me.Shift = "L"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
 
S

SteveS

tina said:
Hi
I have designed 2 forms for user input one for morning shift one for late
shift they input Date noofemployee notemps etc each form has a field called
shift with default value of "M" or "L" .after data is entered user clicks
command button to accept entries which writes data into employee table.
My problem is as date is same data is overwritten .How can I get 2 rows for
each date ??
hope this makes sense
Thanks
Tina
code as follows
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Me.ProductionDate = Me.Text1
Me.Shift = "M"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click
Me.ProductionDate = Me.Text1
Me.Shift = "L"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
Click to expand...

Tina,
You have a bound form with unbound controls. What you are doing is saving the
info on the form to the current record. You need to enter a new record, then
save the data.

You have three options:

1) Make the forms Data Entry Only

2) Add a line to your current code to go to a new record first, then save the data

3) use VBA to create a recordset, add a new record, and update the data.


For option 1, open the forms in design view, Click on the Data tab and change
the property "Data Entry" from NO to Yes. (No code change)

For Option 2, this what your code would look like (for both forms):

' ****beg code***
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

'**************
'ADD THIS LINE
DoCmd.GoToRecord , , acNewRec
'**************

Me.ProductionDate = Me.Text1
Me.Shift = "L"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
"*****end code****

Option 3, is creating a recordset using VBA, then adding a new record and
updating the record.


You still need some validation (or error checking) checking to prevent adding
more than one record with the same date and shift. If you don't do it in code,
you could use a compound primary key, consisting of the ProductionDate and
Shift (set in design view of the table).


I would question using two forms to enter the same data (except the shift -
M/L). Use one form with an option group, list box or combo box to select the
shift. You can have two hidden labels (MORNING SHIFT / LATE SHIFT) that are set
to visible once the shift is selected. Or set the backcolor depending on which
shift was selected.


HTH
 
G

Guest

Hi Steve
Thank you very much greast clear instructions .I am going with option 2
One more question if you can spare the time
I have set key in table as date and shift and if user enters same date and
shift the data is not entered in table which is correct but I would like a
message to tell the user this .
Please could you tell me correct way?
Thanks again
Tina
 
S

SteveS

tina said:
Hi Steve
Thank you very much greast clear instructions .I am going with option 2
One more question if you can spare the time
I have set key in table as date and shift and if user enters same date and
shift the data is not entered in table which is correct but I would like a
message to tell the user this .
Please could you tell me correct way?
Thanks again
Tina
Click to expand...

Hi Tina,

I don't know how (or if you can check) for a key violation, so I usually take
the easy way out and check for a record. Maybe one of the MVPs know.


Below is the modified code for the LATE shift form.

'******* beg code ************
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim rst As DAO.Recordset
Dim strWhere As String

'check if record has been entered already
Set rst = Me.RecordsetClone
' #### change the L to M for the morning shift
strWhere = "ProductionDate = #" & Me.Text1 & "# and Shift = 'L'"
rst.FindFirst strWhere
If Not rst.NoMatch Then
' #### change LATE to MORNING
MsgBox "There was an entry found for " & Me.Text1 & " LATE shift" & _
vbCrLf & vbCrLf & "Entry not allowed!!"
Set rst = Nothing

' you can clear the entries and leave the form open
' so the entries can be changed
' or you can close the form without allowing
' changes by uncommenting the following line
'DoCmd.Close
End If

' no record found - OK to enter new record
DoCmd.GoToRecord , , acNewRec

Me.ProductionDate = Me.Text1
'#### don't forget here also
Me.Shift = "L"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

DoCmd.Close

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

'******* end code ************


HTH
 
G

Guest

Hi
I have only had time to test last macro today and the line
"If Not rst.noMatch Then" seems to result in false though should be true as
I am same entering date and shift again the data is not written into table as
key is date and shift I just cannot seem to get msgbox to appear
any ideas ?
Tina
 
S

SteveS

tina said:
Hi
I have only had time to test last macro today and the line
"If Not rst.noMatch Then" seems to result in false though should be true as
I am same entering date and shift again the data is not written into table as
key is date and shift I just cannot seem to get msgbox to appear
any ideas ?
Tina
Click to expand...

Hi Tina,

When you do a Find, rst.NoMatch is *TRUE* if the desired record was *NOT* found.

I changed the code to get rid of the NOT...it gets confusing at times. :)

I set up a table and form based on your first post. The following code will not
allow two entries on the same date for the late shift (or the morning shift)
but *will* allow Morning and Late entries for the same day.

NOTE there is separate code for the MORNING entry form and the LATE entry Form.


'******* beg code LATE SHIFT ************
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim rst As DAO.Recordset
Dim strWhere As String

'check if record has been entered already
Set rst = Me.RecordsetClone
strWhere = "ProductionDate = #" & Me.Text1 & "# and Shift = 'L'"
rst.FindFirst strWhere
If rst.NoMatch Then
' no record found - OK to enter new record
DoCmd.GoToRecord , , acNewRec

Me.ProductionDate = Me.Text1
Me.Shift = "L"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

Set rst = Nothing
DoCmd.Close

Else
'record found!!
MsgBox "There was a LATE shift entry found for " & Me.Text1 & _
vbCrLf & vbCrLf & "Entry not allowed!!" & vbCrLf & vbCrLf & _
"Is the Date correct??"
Set rst = Nothing

' you can clear the entries and leave the form open
' so the entries can be changed
' or you can close the form without allowing
' changes by uncommenting the following line
'DoCmd.Close
End If


Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
'******* end code ************



'******* beg code MORNING SHIFT ************
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim rst As DAO.Recordset
Dim strWhere As String

'check if record has been entered already
Set rst = Me.RecordsetClone
strWhere = "ProductionDate = #" & Me.Text1 & "# and Shift = 'M'"
rst.FindFirst strWhere
If rst.NoMatch Then
' no record found - OK to enter new record
DoCmd.GoToRecord , , acNewRec

Me.ProductionDate = Me.Text1
Me.Shift = "M"
Me.NoOfEmployees = Me.Text7
Me.NoOfTemps = Me.Text13
Me.NoOfPartTemps = Me.Text19
Me.NoOfPartTempsHours = Me.Text21

Set rst = Nothing
DoCmd.Close

Else
'record found!!
MsgBox "There was a MORNING shift entry found for " & Me.Text1 & _
vbCrLf & vbCrLf & "Entry not allowed!!" & vbCrLf & vbCrLf & _
"Is the Date correct??"
Set rst = Nothing

' you can clear the entries and leave the form open
' so the entries can be changed
' or you can close the form without allowing
' changes by uncommenting the following line
'DoCmd.Close
End If


Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
'******* end code ************
 
G

Guest

Hi Steve
Thank you for your time but I am still not getting correct result
even though the production date and shift are in table the line
if rst.NoMatch then
is returning true
when i move my cursor over set rst = me.RecordsetClone nothing appears
unlike when move over
strWhere which shows date and shift
I am now Totally confused
I have set libraries and tried reading the help to no avail
If you have any further ideas I would be very glad to hear them
Thanks again for your time
Tina
 
S

SteveS

tina said:
Hi Steve
Thank you for your time but I am still not getting correct result
even though the production date and shift are in table the line
if rst.NoMatch then
is returning true
when i move my cursor over set rst = me.RecordsetClone nothing appears
unlike when move over
strWhere which shows date and shift
I am now Totally confused
I have set libraries and tried reading the help to no avail
If you have any further ideas I would be very glad to hear them
Thanks again for your time
Tina
Click to expand...

Tina,

I am at a loss as to why the code works for me but not for you.

What Version of Access are you using? (I have A2K)

Is there a web site where you put the mdb so I can download it (minus sensitive
data)? Or do a compact & repair, then Winzip it and email it to me? Maybe just
the three forms and the related tables/queries??

If I see it maybe I can find to problem.
 
G

Guest

Thanks Steve
I have managed to achieve what I want be using your option one and some of
other suggestions its not as sophicated as your way but it will do for my
purpose
Thanks again for your time
Tina
 
S

SteveS

tina said:
Thanks Steve
I have managed to achieve what I want be using your option one and some of
other suggestions its not as sophicated as your way but it will do for my
purpose
Thanks again for your time
Tina
Click to expand...

You're welcome. Sorry I wasn't able to be of more help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Help with Microsoft Access Form Design. 0
FileSystemObject - Compile Error Invalid use of property 1
Open Excel Sheet By button / text box data. 4
view/ print multi reports 2
DoCMD maximize 3
Error after exporting forms to new db 2
Help with VBA code to run update query and open form. 2
Report Data Splitting 2

Top