DoCmd.FindRecord for lookup combos (1 Viewer)

Solo712

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
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
 

Attachments

  • Custom FindRecord form.jpg
    Custom FindRecord form.jpg
    64.3 KB · Views: 221
Minty

Minty

AWF VIP
Local time
Today, 05:59
Joined
Jul 26, 2013
Messages
10,371
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

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
Minty said:
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.
Click to expand...

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
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 21:59
Joined
Sep 12, 2017
Messages
2,111
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

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
Mark_ said:
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?
Click to expand...

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

jdraw

Super Moderator
Staff member
Local time
Today, 00:59
Joined
Jan 23, 2006
Messages
15,378
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.
 
Last edited:
M

Mark_

Longboard on the internet
Local time
Yesterday, 21:59
Joined
Sep 12, 2017
Messages
2,111
Solo712 said:
And yes, all my combos are constructed with the Lookup wizard, i.e. are driven off a single table.
Click to expand...

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

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
jdraw said:
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.
Click to expand...

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
 

Attachments

  • FindRec.zip
    202.5 KB · Views: 88
Last edited:
Solo712

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
Mark_ said:
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?
Click to expand...

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

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
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
Code: 
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:

Code: 
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

jdraw

Super Moderator
Staff member
Local time
Today, 00:59
Joined
Jan 23, 2006
Messages
15,378
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

Solo712

Registered User.
Local time
Today, 00:59
Joined
Oct 19, 2012
Messages
828
jdraw said:
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 !
Click to expand...

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.

Code: 
'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
 
You must log in or register to reply here.

Similar threads

A
Solved On Subform2 within Subform1 : Criteria for a lookup Query (to refer to a control on Subform1)
Replies
3
Views
402
alan2013
A
N
Solved Rules to be implemented with Ms Access against SQL Server Cloud
Replies
2
Views
643
Pat Hartman
P
C
Embedding Reports on to Forms, is it still possible?
Replies
8
Views
392
isladogs
isladogs
Z
Autofill From Combo Box In Form Not Populating Table
Replies
3
Views
614
moke123
moke123
S
Solved Issue with acDialog on Adding a Record
Replies
4
Views
502
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom