DoCmd.FindRecord for lookup combos

[Solo712]

I am designing a custom generalized FindRecord form (see attached pic) to replace Access Find Record dialogue which I had many (justified) complaints about.

Everything seems to work fine except: The DoCmd.FindRecord method does not (seem to) handle controls with searcheable combos.

First request: please confirm that there is no way to do it via DoCmd.FindRecord. Maybe, I am missing something. The Find Record dialogue handles those combos just fine.

Second request: (,in case I am not missing anything about the method). Is there any existing tested code that you know about that would manage finding records by controls with lookup values ?

Thanks in advance.

Jiri

 

[Minty]

If you mean searching lookup Fields in your tables then you are going to be a bit up a river without a paddle.

If Access does find the related text in them using it's inbuilt search, then it is doing some clever shizzle in the background.

I cant think of a way of doing that that is simple, or given some longer thoughts, even a complicated way of doing it.

 

[Solo712]

If you mean searching lookup Fields in your tables then you are going to be a bit up a river without a paddle.

If Access does find the related text in them using it's inbuilt search, then it is doing some clever shizzle in the background.

I cant think of a way of doing that that is simple, or given some longer thoughts, even a complicated way of doing it.

Thanks, Minty. Yes, that would be the pessimist's point of vue. But the point is
that after spending a couple days with this, I brought it to AWF precisely in the hope of seeing some "clever shizzle".

Best,
Jiri

 

[Mark_]

For "handle controls with searcheable combos", what value is kept in the field? Is the the value returned or a reference?

i.e. Does [LockerNum] hold the value "SML 2" or does it reference "SML 2" by record ID?

If you DO reference by record ID, are all of your lookups driven off of one table?

 

[Solo712]

For "handle controls with searcheable combos", what value is kept in the field? Is the the value returned or a reference?

i.e. Does [LockerNum] hold the value "SML 2" or does it reference "SML 2" by record ID?

If you DO reference by record ID, are all of your lookups driven off of one table?

Thanks, Mark. By "searcheable combos" I mean combos that store (via query) the bound field value in the first lookup column but display the second value. Most of the row sources for such controls have the SQL statement like SELECT [tblname].[boundfield], [tblname].[displayfield] ORDER BY....
In the example that I sent the "SML 2" search string represents the value that has to occur at the start of the field (acStart) in form "Storage1" current field "LockerNum". If I hit the Find First button, the Storage1 form would display "SML 201". Note that the form identified the field as a 'TextBox'. I have the logic worked out for control types acTextBox (if bound) and acComboBox (if it has one column). I am looking for an optimal solution for acComboBox with more than one column, ie where the stored value <> displayed value. I am searching by the display value.

I would be looking for a function that returns reference to the matched record.

And yes, all my combos are constructed with the Lookup wizard, i.e. are driven off a single table.

Best,
Jiri

 

[jdraw]

Can you post a sample database with this set up? Might be easier to do a little "trial and error".

You might be able to look for such combos; get the rowsource(s), and create a recordset(s)(s)?? and search against the result???? Just a thought.

I am not aware of any proven reliable code to do the find first on the display field(s) where the bound column is the Id.

 

[Mark_]

And yes, all my combos are constructed with the Lookup wizard, i.e. are driven off a single table.

Are they all drive off of the SAME table or different tables?

i.e. do you have ONE "Lookup table" that you can query against or many you would need to deal with?

 

[Solo712]

Can you post a sample database with this set up? Might be easier to do a little "trial and error".

You might be able to look for such combos; get the rowsource(s), and create a recordset(s)(s)?? and search against the result???? Just a thought.

I am not aware of any proven reliable code to do the find first on the display field(s) where the bound column is the Id.

Demo database attached. I have had the same general approach as you are advancing and as a matter of fact I think I have a working prototype. I will not show here it though until I have done some more testing. Since I intend this to go into my commercial creations I just want to make sure a) it works, b) I'm not re-inventing a wheel that someone might have better tires for.
So for now I am just showing a stub for the multi-column combos. By the way, for the purposes of of this exercise I call combo boxes with user defined values "Text lists". There is one example of such a control in the form I attached and it's called "LockerType".

After opening the database start with the storage form.

Best,
Jiri

 

[Solo712]

Are they all drive off of the SAME table or different tables?

i.e. do you have ONE "Lookup table" that you can query against or many you would need to deal with?

They drive off whatever you want them to. It's a generalized utility you should be able to throw into any table-based form. You can have many many combos on the form each joining with a different table.

Best,
Jiri

 

[Solo712]

Oops, it appears that I have sent an earlier version just after created the second button, where I provisionally copied the code from the first to the second one. Looked ugly. So I changed it but I zipped the wrong file.

So the two "Find" Button Clicks in frmFindRec should look like this

Private Sub FFButton_Click()
   'Find First button
   DispatchResponse (1)
End Sub
'
Private Sub FNButton_Click()
  'Find First button
  DispatchResponse (2)
End Sub

...and the common dispatch routine like this:

Private Sub DispatchResponse(trig As Long)   
   'check first if form has any searcheable items,
   'and second if a search string has been supplied
   If FieldList.ListCount = 0 Then Exit Sub
   If Nz(SearchWhat) = "" Then
      MsgBox "Search string must be supplied!", vbExclamation, "Invalid argument!"
      Exit Sub
   End If
  'load search arguments into variables
  ' which part of the field are we searching ?
   Select Case frSearchMethod
          Case 1
             glbSearchMethod = acStart
          Case 2
             glbSearchMethod = acAnywhere
          Case 3
             glbSearchMethod = acEntire
   End Select
  glbResponse = SearchWhat
  glbControlType = FieldList.Column(1)
  'if a multi combo then separate the SQL elements in the Row Source
  If Left(glbControlType, 1) = "c" Then CutTheFuckerUp
  ' 1 = find first, 2 = find next
  glbTrigger = trig
End Sub

Sorry for the kerfuffle.

Jiri

 

[jdraw]

I modified the database with the code you provided. It seems to work for me. I could even find records using other lockerxxx controls.

Carry on !

 

[Solo712]

I modified the database with the code you provided. It seems to work for me. I could even find records using other lockerxxx controls.

Carry on !

jdraw,
you may pick up any of the controls shown in the ListBox in frmFindRec without returning to the requesting form: that's the whole idea. You selecting a Search Field, will make it automatically the current field on return into the execution loop in the requester form (frmStorage).

This line in FindRec_Click does the trick.

'reset the active control: user may have changed it in frmFindRec
            Me(glbActiveControl).SetFocus

One note: there is something that looks little glitchy with looking up "find first" when the first matching record is actually displaying. You will get the message "No (other) record found !", which, if you think about it, is correct.

Any concrete idea yet how to do the query/table combo boxes ?

Best,
Jiri