Search before you add

[Huw Davies]

Can anyone give me some ideas as to how to force a user to search for an existing client, open a form showing that client's record if found, show an empty form for the addition of a new client if not found.

I've tried putting a dummy record as the first record in my client table but my dear users just start typing and keep overwriting it.

Any suggestions gratefully received (the simpler the better - I'm an old man!)

Thanks

Huw

 

[revver]

Hi Huw,
I'm not sure I have enough info about your setup but you could try this.

I have an application that opens to a form with only a single text box which accepts a fixed length client identifier. I have set the AutoTab property to yes. I have code in the AfterUpdate event of the textbox which opens another form showing the record for the ClientID the user entered.

Your AfterUpdate code chould choose which form to open or which mode to open a form in, add or edit. The text box could be a combo to select an existing client.

 

[Huw Davies]

Len,
Thanks for this, I'll try this. Could you tell me how do you choose one action if you've found a record or another one if you haven't?

Huw

 

[revver]

Hi Huw
Code similar to this should go in the BeforeUpdate event of your search form.

Dim stDocName As String
Dim stLinkCriteria As String
Dim varLookup As Variant
On Error GoTo Err_TxtAsk_BeforeUpdate
    
varLookup = DLookup("ClientID", "tblClients", "ClientNum = '" & Me.TxtAsk & "'")
    
If IsNull(varLookup) Then                 'client record doesn't exist
    Cancel = True                         'cancel updating this search form
    'Replace this line for a different action other than just telling them
    MsgBox "You've made a typo. No such client number as " & Me.TxtAsk
Else                                      'client record exists 
    stDocName = "frmClientEdit"           'set the name of the form to open
    stLinkCriteria = "ClientNum = '" & Me.TxtAsk & "'"  'say what record to find
    DoCmd.OpenForm stDocName, , , stLinkCriteria        'open the edit form
    DoCmd.Close acForm, "frmSearch", acSaveNo           'close the search form
End If

Exit_TxtAsk_BeforeUpdate:
    Exit Sub
    
Err_TxtAsk_BeforeUpdate:
    MsgBox Err.Description & vbCrLf & "Error Num:" & Err.Number
    Resume Exit_TxtAsk_BeforeUpdate

If you want, rather than tell them about a typo, you could replace the MsgBox line with a statement block similar to the Else clause to open a different form or the same form in add mode .