Record fills in if it exists

[laurat]

The way my form is set up now, it is bases off a query that prompts the user for a SO Num. If the number exists the form opens displays the first record with that SO Num and they can flip through all other records with that number. If the SO Num does not exist the form opens blank and they can fill in the data. However I do not like this set up. Is there any way that the form can just open normal and the user can enter the SO Num they want directly into the SO Num field on the form, if the number exists the form fills in with the first record with that SO Num and they can flip through the others, if it does not exist, the form remains blank and they can fill it in???

Thanks

 

[Autoeng]

Create a combo box with the wizard turned on. Select look up values in a table or query. You will then be propted for the table or query and field. So simple isn't it? It will the display in the drop down all off the records from that table and if you type in one and it doesn't exist you can enter a new one. You could add code to the after update command of the field to display a message before entering a new record in case they mistyped and existing one. I have some code to do this if you want it.

Autoeng

 

[Dave Eyley]

While I was away thinking about this one, an answer arrived.

But I could still give you mine anyway...

Create a Mainform with one field on it called 'SONo'
Create a subform and populate it with all the fields from the table including 'SONo'.

Drag the subform onto the mainform and use the Master/Child linking property on the subform to link them using the, yes you guessed it, 'SONo' field.

Open the form and type any SoNo into the single field on the mainform. The subform updates automatically if there's records in the table relating to that SONo. If not the field in the subform called 'SONo' is already filled in and the record is ready to complete.

It may be better, if there aren't too many fields, to have the subform as a Datasheet so you would immediately see the records in a list. You could also do it as a continuous form, just depends on aesthetics really.

Dave Eyley

 

[Autoeng]

Everybody is on the ball today with answers. Dave's response will work equally as well as mine but I give you a drop down box to select a record from and a message if the record doesn't exist. It's up to you which way you want to go. No coding experiece?, go with Dave's.

Autoeng

 

[laurat]

I will most likely try both solutions and see which works best for me. Thank you both for your input.

Autoeng, I am interested in any code you can supply me with to get this done. Thanks.

 

[Autoeng]

Laurat:

Below find the code I use as an AfterUpdate event to the combobox that you created. Text in blue is where you need to substitute your names. This code is set to look at whether the user is a member of a security work group that you specify to decide which error message to display if they enter a record that does not exist. It also calls another event Make_Visable so that until the user enters a record number no other fields will be displayed. This requires that you create the Make_Visible event and a Make_Invisible event. I put the Make_Invisible code below as well. For the Make_Visible code change "False" to "True". The Make_Invisible is a OnLoad event.

This is the AfterUpdate event
Private Sub yourcombobox_AfterUpdate()
Dim varReturn As Variant

Me.RecordsetClone.FindFirst "[yourSOnumfield] = '" & Me![yourcombobox] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.yourSOnumfield <> Me.yourcombobox Then
If InStr(UserGroups(), "youradmingroup") <> 0 Then
varReturn = MsgBox("The record that you entered does not exist." & vbCrLf & _
"You are about to enter a new record into the database." & vbCrLf & _
"Is this what you want to do?", vbYesNo)
If varReturn = vbYes Then
DoCmd.GoToRecord , , acNewRec
Call Make_Visible
Me.yourSOnumfield= Me.yourcombobox
Me.yournextfieldafterSOnumfield.SetFocus
Else
Me.yourcombobox = ""
Me.yourcombobox.SetFocus
End If
Else
MsgBox ("The record you entered does not exists." & vbCrLf & _
"You do not have the necessary permissions to create a new record." & vbCrLf & _
"Please see the system administrator for assistance.")
End If
Else
Call Make_Visible
Me.yournextfieldafterSOnumfield.SetFocus
End If

End Sub


This is the OnLoad event
Public Sub Make_Invisible()

Me.yournextfieldafterSOnumfield.Visible = False
'keep repeating with your other field names until you have listed all but yourcombobox

End Sub

Autoeng