Paramater Search

[GregB]

I would like for a user to be prompted to enter a serial number, if the DB
has a record with that Serial Number than it will display the information
(I know how to do that, just use the like command )

But, I would like it to be that if the serial number typed in is not
currently in the DB then the user can create a new form based on the serial
number they just scanned in..... I want it to populate the field on the form.
(right now they have to scane it in twice, ounce when prompted for paramater
and again when the form opens)

How would I go about this?

Thanks Much!

 

[Wayne-I-M]

Hi Greg

Following on from Al's suggestion, another method would be to have a combo
box with the serial numbers in. Type in the number or text and if it is not
there create a new record.

In all the code I assume you have a control called [serial number]

After Update below

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[serial number] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End If
Me.ComboSearch = ""
Me.serial number.SetFocus
End Sub


Not in list below


Private Sub ComboSearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'" & NewData & "' is not is database." & vbCr & vbCr
msg = msg & "Do you want to add New Record?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "have another go."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TableName", dbOpenDynaset)
rs.AddNew
rs![serial number] = NewData
rs.Update
Response = acDataErrAdded
Me.ButtonConfirmNew.Visible = True
End If
End Sub

As you can see I have put a "confirm" button section in the code so that if
a mistake has been made it can be cancelled prior to new record being made.
The onclick of the confirm button would look something like this

On Click

Private Sub ButtonConfirm_Click()
DoCmd.Requery ""
DoCmd.GoToRecord , "", acLast
Me.ButtonConfirmNew.Visible = False
Me.serial number.SetFocus
End Sub

Although you can leave the confirm button out if you don't like it. It just
seems a good idea to me.

Hope this helps

 

[GregB]

Thanks a lot, works great!

Hi Greg

Following on from Al's suggestion, another method would be to have a combo
box with the serial numbers in. Type in the number or text and if it is not
there create a new record.

In all the code I assume you have a control called [serial number]

After Update below

Private Sub ComboSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[serial number] = " & Str(Me![ComboSearch])
Me.Bookmark = rs.Bookmark
End If
Me.ComboSearch = ""
Me.serial number.SetFocus
End Sub


Not in list below


Private Sub ComboSearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'" & NewData & "' is not is database." & vbCr & vbCr
msg = msg & "Do you want to add New Record?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "have another go."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TableName", dbOpenDynaset)
rs.AddNew
rs![serial number] = NewData
rs.Update
Response = acDataErrAdded
Me.ButtonConfirmNew.Visible = True
End If
End Sub

As you can see I have put a "confirm" button section in the code so that if
a mistake has been made it can be cancelled prior to new record being made.
The onclick of the confirm button would look something like this

On Click

Private Sub ButtonConfirm_Click()
DoCmd.Requery ""
DoCmd.GoToRecord , "", acLast
Me.ButtonConfirmNew.Visible = False
Me.serial number.SetFocus
End Sub

Although you can leave the confirm button out if you don't like it. It just
seems a good idea to me.

Hope this helps




--
Wayne
Manchester, England.

I would like for a user to be prompted to enter a serial number, if the DB
has a record with that Serial Number than it will display the information
(I know how to do that, just use the like command )

But, I would like it to be that if the serial number typed in is not
currently in the DB then the user can create a new form based on the serial
number they just scanned in..... I want it to populate the field on the form.
(right now they have to scane it in twice, ounce when prompted for paramater
and again when the form opens)

How would I go about this?

Thanks Much!