Pop-Up for primary key prompt 2

[sdlewis]

I have a database where the user will enter several numerical values and will be given a calculated value based on those entered. I have the form set to bring up a fresh form each time (data entry = yes). I want the user to be forced to enter the primary key (in this case a serial number and the date) at the beginning of each record entry. How do I go about doing this?

 

[ToeShot]

How is the primary key generated? Can it be automaticaly put in by the program?

 

[Taff07]

If you set the control box to be the first to receive focus on opening the form, then try using the On Lost Focus Event to run some Checks to see if the Control holds the data required and display a message if they havn`t.

Did you need to check if the entry is already in the table at this point or are you assuming this isn`t a problem?

 

[sdlewis]

ToeShot- the serial numbers are non-sequential and are letters and numbers, so I don't think it can be automated.
Taff7- I defined the primary key as both the serial number and the date, so I'm not expecting overlap to be a problem.
I also need assistance in how to create a pop-up box to begin with. Thank you.

 

[RemyS]

Would an input box in the Form_Open or _Click event be what you're looking for? Still new to DB's and enjoying learning day by day

 

[ToeShot]

Taff07's Solution will work. I don't know if you know any VBA but I will give you what you need and if you need to know how to implement it let me know.

on the forms OnCurrent() event you would type

Me![{Serial Number Field}].SetFocus

Now in the {Serial Number Field} text box you would put this code on the Lost Focus event

If IsNull(Me.{Serial Number Field}) Then
MsgBox "You Need To Enter A Serial Number. Please Enter A Serial Number",,"Enter Serial Number"
Me![{Serial Number Field}].SetFocus
Else
Me![{Date Box Field}].SetFocus
End If

The you would put that same statement in the {Date Box Field} like so

If IsNull(Me.{Date Box Field}) Then
MsgBox "You Need To Enter A Date. Please Enter A Date",,"Enter Date"
Me![{Date Box Field}].SetFocus
End If

This will check and see if there was data entered into the Serial Number field if not it will give in error message until there is data put in then it will move to the date box and do the sanme thing. This does not Validate the serial number.
HTH

 

[sdlewis]

For the Form, I put
Me![ShaftSN].SetFocus
in the On Current part.
In the On Lost Focus part of the ShaftSN Text Box is the Event Procedure, and the same goes for the Date Text Box. The Class Module is:

Private Sub Date_LostFocus()
If IsNull(Me.ShaftSN) Then
MsgBox "You Need To Enter A Serial Number. Please Enter A Serial Number", , "Enter Serial Number"
Me![ShaftSN].SetFocus
Else
Me![Date].SetFocus
End If
End Sub

Private Sub ShaftSN_LostFocus()
If IsNull(Me.Date) Then
MsgBox "You Need To Enter A Date. Please Enter A Date", , "Enter Date"
Me![Date].SetFocus
End If
End Sub

I save the Form, and when I go the entry view (in data entry mode), it gives the error message that it can't find the macro 'Me!ShaftSN.' What do I need to do now? Thank you for helping me.

 

[ToeShot]

I'm sorry I do not know why it would look for a macro, it should not do that.
Also the Code I supplied will not work. I am sorry about the bad information.

The code below I believe will be the best solution. You would put this on your OnLoad event Of the data entry form.

Dim strShaftSN As String
Dim strDate As String

strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot
While (strShaftSN = "&quot
strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot
Wend
Me.ShaftSN= strShaftSN

strDate = InputBox("Please enter date", "Enter Date&quot
While (strDate = "&quot
strDate = InputBox("Please enter date", "Enter Date&quot
Wend
Me.Date = strDate

This will have a box asking for the ShaftID until one is put in then it will do the same for the date. The user can not cancel out or close this box. They also can not edit the data entry form.

Note if your date field is stored in date format you need to have the las line look like this. And The date must be a valid date format.

Me.Date = CDate(strDate)
HTH Sorry About the bad info the first time. It was 3A.M.

 

[sdlewis]

That code worked great! Thank you so much. I would also like to have the boxes pop up if it goes to a new record. Can I put that same code under another event to do that? Thanks again.

 

[ToeShot]

Yes

 

[sdlewis]

Which event?

 

[ToeShot]

in the Oncurrent Event put this

If Me.NewRecord Then

Dim strShaftSN As String
Dim strDate As String

strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot
While (strShaftSN = "&quot
strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot
Wend
Me.ShaftSN= strShaftSN

strDate = InputBox("Please enter date", "Enter Date&quot
While (strDate = "&quot
strDate = InputBox("Please enter date", "Enter Date&quot
Wend
Me.Date = strDate

End If

Notice that i just added an if statement around the original code saying if it is a new record then run the pop-ups if not just continue as normal.

HTH

 

[sdlewis]

That code again worked very well. Thank you. If I hit cancel in the pop-up box, it won't let me cancel out. How can I make this work? The only thing I can think of is not having it set to data entry, but then other things won't work.

 

[ToeShot]

I am assuming that you ar useing the last piece of code I gave you.

in the Oncurrent Event put this

If Me.NewRecord Then

Dim strShaftSN As String
Dim strDate As String

strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot

[red] If strShaftSN = vbCancel Then
Exit Sub
End If [/color]

While (strShaftSN = "&quot
strShaftSN = InputBox("Please enter ShaftSN", "Enter Shaft Serial Number&quot
Wend
Me.ShaftSN= strShaftSN

strDate = InputBox("Please enter date", "Enter Date&quot

[red] If strDate = vbCancel Then
Exit Sub
End If [/color]

While (strDate = "&quot
strDate = InputBox("Please enter date", "Enter Date&quot
Wend
Me.Date = strDate

End If

May want to check the help files But I am pretty sure I have syntax right. Glad the previous code worked for you and I Hope This Helps

Dave