Dlookup - null value problem

[50ld13r]

Hi

im tying to create a search function that will find all the details of a customer when i enter there account number. So far i can return the name of the customer when i enter in a valid account number but am unsure how to solve the problem when an invalid account is entered. Also this only returns one field at a time- is there a more efficient way i could assign the customers records to the corresponding text boxes,for example the code below will reurun the name and assign it to the name text box but would i have to do multiple dlookups to display the forename,job title and occupation details in there relevant text boxes

I am currently using:

Private Sub cmdSearch_Click()
Dim search As String

If IsNull(Me.txtAccNum) Or Me.txtAccNum = "" Then
MsgBox "You must enter an Account Number.", vbOKOnly, "Required Data"
Me.txtAccNum.SetFocus
'Exit Sub
Else

txtCustomerName = DLookup("strName", "tblCustomers", "intAccNum=txtAccNum")

End If

End Sub


any directing would be great thanks

 

[Bodisathva]

wrap your DLookup in an NZ. Use the NZ to determine the null value.

 

[modest]

Make References to Microsoft DAO Library in the Tools>>References menu

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SQL QUERY HERE")
Me.txt1 = rs.Fields("The field name in the query/table")
Me.txt2 = rs.Fields("The field name in the query/table")

 

[50ld13r]

im assuming then this:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SQL QUERY HERE")
Me.txt1 = rs.Fields("The field name in the query/table")
Me.txt2 = rs.Fields("The field name in the query/table")

would be alot faster that this:

Me.txt1 = DlookUp("The field name in the query/table")
Me.txt2 = DlookUp("The field name in the query/table")

is that correct? Thanks for nz function works a treat.

 

[MarkK]

To return all the fields create a form that is bound to the table in question. If you already have a bound Customers form, use that.

Create an unbound tbAcctSearch textbox and a cmdSearch button. Then, as per modest's post, leverage the recordset exposed by the form to return the data you want.

Your cmdSearch_Click() event handler looks something like...

Private Sub cmdSearch_Click()
[COLOR="Green"]  'numeric check on user supplied search data[/COLOR]
  If IsNumeric(Me.tbAcctSearch) Then
[COLOR="Green"]    'leverage the recordset of the form[/COLOR]
    With Me.Recordset
[COLOR="Green"]      'run it's FindFirst method against intAccNum and supplied search data[/COLOR]
[COLOR="Green"]      'if it succeeds the found record becomes current[/COLOR]
      .FindFirst "intAccNum = " & Me.tbAcctSearch
[COLOR="Green"]      'return a message if not found[/COLOR]
      If .NoMatch Then MsgBox "Account '" & Me.tbAcctSearch & "' not found."
    End With
  Else
[COLOR="Green"]    'return a message if num is not valid[/COLOR]
    MsgBox "Invalid account number"
  End If
End Sub

Hope this offers more ideas,

 

[50ld13r]

i truely am a newbie thanks for all your help do far, i just cant seem to find how to pass SQL into the Openrecordset function. Where do u guys serach to find these answers as i dont like having to be dependent on your input )but thanks for any direction) Anyway i have include what i took from what was put in the previous post - get an error on when i set rs - probably more errors in there 2

Private Sub cmdSearch_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT strName FROM tblCustomer WHERE intAccNum = txtAccNum")

'numeric check on user supplied search data
If IsNumeric(Me.txtAccNum) Then
'leverage the recordset of the form
With Me.Recordset
'run it's FindFirst method against intAccNum and supplied search data
'if it succeeds the found record becomes current
.FindFirst "intAccNum = " & Me.txtAccNum
'return a message if not found
If .NoMatch Then MsgBox "Account '" & Me.txtAccNum & "' not found."
End With
Else
'return a message if num is not valid
MsgBox "Invalid account number"
End If

End Sub

 

[MarkK]

Do you have a form in which you are able to view a complete record of a customer and perhaps move from one customer to the next?
If so, then the code I supplied should work on that form as posted (with the addition of the indicated controls).
If not, then my suggestion is build that form first. Keep me posted.
Mark

 

[50ld13r]

yeah got what you meant - worked fine -thanks for the help. Will no doubt be here again to pick your brains - thanks again

 

[MarkK]

Cheers, always glad when it works out in the end.