Find Record From Combobox (1 Viewer)

[moose]

i have a ComboBox that looks for a record on my form but when i select a customer it gives me a no record error but when i display the form in design view and then change back to form view, the records are diplayed and it works fine
I only get the error when i first open the form
Any ideas please?

 

[Mile-O]

What code do you have on the AfterUpdate of your combobox?


P.S. I renamed the thread because, although it may be "urgent" (three times, no less) to you, I'm sure nobody else feels it is worthy of a quick response. There's no deadlines with other peoples' work.

 

[moose]

sorry about the thread title, i just have to get this done by 2 o'clock
the code i have in the after update is

Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerID] = " & Str(Me![Combo6])
    Me.Bookmark = rs.Bookmark
    TblCusProdSubform1.Visible = True
End Sub

 

[CBragg]

In my experience its easy to base a query on the data then find it from the Unique ID you've set.

Alternatively ive put the Combo box on a main form and the other data you want to look up on a sub-form.

Thats usually sorted it for me anyway.

Hope this is of help!!

 

[Mile-O]

Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerID] = " & Str(Me![Combo6])
    Me.Bookmark = rs.Bookmark
    TblCusProdSubform1.Visible = True
End Sub

A few things.

Dim rs As Object is not explicit. Always be explicit.

Dim rs As DAO.Recordset

Recordset.Clone ?
I was always under the impression it was RecordsetClone

rs.FindFirst "[CustomerID] = " & Str(Me![Combo6])

Three things here:

1. You are changing a value to a string but not using the correct delimiters.
2. You are using Me! when you've used Me. previously in the same sub. Be consistent. In this case you use Me.
3. Get a better name than Combo6. It's not descriptive of the control's purpose.

TblCusProdSubform1.Visible = True

The Subform is an object on the form. Use Me. to explicitly reference it.

Me.TblCusProdSubform1.Visible = True

Also, remember to destroy the reference to the recordset object otherwise it will linger in the memory.

Set rs = Nothing


Two final things:

• Put some error handling in here.
• It might be wise to set the recordset to the first record in the recordsetclone. rs.MoveFirst
  
  
  

  Private Sub Combo6_AfterUpdate()
  
      On Error Goto Err_ErrorHandler
  
      ' Find the record that matches the control.
      Dim rs As DAO.Recordset
  
      Set rs = Me.RecordsetClone
  
      With rs
          .MoveFirst
          .FindFirst "[CustomerID] = """ & Str(Me.[Combo6]) & """"
          Me.Bookmark = .Bookmark
      End With
  
      Me.TblCusProdSubform1.Visible = True
  
  Exit_ErrorHandler:
     Set rs = Nothing
     Exit Sub
  
  Err_ErrorHandler:
      MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
      Resume Exit_ErrorHandler
  
  End Sub

 

[moose]

i tried your code and it threw up the following error
Compile Error:
User-Defined type not defined

 Dim rs As DAO.Recordset

 

[Mile-O]

Open a module, goto tools -> References, check the Microsoft DAO reference

And read this

 

[moose]

i am using access 2k and dont have references on the tools menu

 

[moose]

sorry i didnt have the module open