Paramater Search

[Guest]

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!

 

[Al Campagna]

Greg,
I'll assume that you are prompting for the SN through an Inputbox?
Even if it's a form... the procedure would be very similar.
Inputbox input variable named SerNo. (aircode)

Prompt via Inputbox for SerNo
Dlookup SerNo agianst your SerialNo table
Does exist?
Run normal code
Doesn't exist?
MsgBox "Doesn't Exist"
OK?
Open the other form.
GoToRecord new record
SerialNo = SerNo
Cancel?
ExitSub

You could also use another technique. Allow the user to select a
SerialNo from a combobox, the query behind which, will only display
lergitimate SerialNos for selection. Then use the OnNotInList to trigger
the new record code....

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Guest]

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

 

[Guest]

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!