Access Form

Have Form A contains a text field where user enters a string and stores string to memory variable.
Have Form B whose record source is Query B.
Query B returns the following records:

Class   Color
1          Blue
2          Brown
3          Gold
4          Red
5          Green

User enters text in Form A.  User clicks command button that opens Form B.  Is there some way to open Form B to Color that matches string that user entered in Form A?  Would like to navigate through all records in Form B, but open Form B to Color that matches string that user entered in Form A.

Know that I can filter on string entered by user, but that would prevent me from navigating through all records.
clock1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
from form A, open form B with

docmd.OpenForm "formB",,,,,,Me.txtColor  

txtColor is the name of the textbox in form a where you enter the color (change the name accordingly)


now in the load event of "formB", place this codes

private sub form_load()

if me.openargs & ""<>"" then
   with me.recordsetclone
       .findFirst "[Color]='" & me.openargs & "'"
      if not .nomatch then
          me.bookmark=.bookmark
          else
          msgbox "No Matching record!"
       end if
   end with
end if
end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clock1Author Commented:
Rey,

This load event locates the appropriate record and displays it, but sets Filter On so that I cannot navigate past the located record.
0
Rey Obrero (Capricorn1)Commented:
<but sets Filter On > 

this should not happen, there is something going on in your form.

try this,
open formB in design view, click on the Data tab and look for the Filter property and delete the entry if theres any.

set the Filter On Load property to NO
save the form
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

clock1Author Commented:
Did as you advised, still shows Filtered.
0
clock1Author Commented:
I clear everything in Filter property.
When user enters [Color] Access shows Filter property to [Color]=Me![Color]
Fetch Defaults and Order By on Load are set to yes.
0
Dale FyeCommented:
Rey's technique is the correct one to use for this circumstance.  I would not be surprised to find some other code either in FormB or in FormA that is setting the FilterOn property of formB to true at runtime.

Can you post a sample database with only FormA, Formb, and the table and query that are used by FormB.

Best method to do this is to open a new database and import those 4 items.  Then test to make sure that everything runs properly and that you can duplicate the problem in the new database.   Then remove any proprietary or private data from the sample database, compact and repair it, and post it here.
0
clock1Author Commented:
I'll do so.  Just quick comment:
After user enters [Color] on Form A,  I open Form B using DoCmd.OpenForm FormB
0
clock1Author Commented:
Found way to pass memory variable for Color from one form to next.  Points are yours.  Thanks!
0
Dale FyeCommented:
Clock1,

What Rey told you to do in his post was to use the OpenArgs argument (the last argument) in the OpenForm method.

docmd.OpenForm "formB",,,,,,Me.txtColor  

This is one of the many ways to pass a value to another form.  You did not need to save that value to a variable, you simply needed to pass it to FormB usint this technique.

Dale
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.