Solved

Access Form

Posted on 2014-11-30
9
409 Views
Last Modified: 2014-12-01
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.
0
Comment
Question by:clock1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40472644
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
 

Author Comment

by:clock1
ID: 40472669
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40472673
<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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:clock1
ID: 40472683
Did as you advised, still shows Filtered.
0
 

Author Comment

by:clock1
ID: 40472688
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40472693
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
 

Author Comment

by:clock1
ID: 40472701
I'll do so.  Just quick comment:
After user enters [Color] on Form A,  I open Form B using DoCmd.OpenForm FormB
0
 

Author Comment

by:clock1
ID: 40472733
Found way to pass memory variable for Color from one form to next.  Points are yours.  Thanks!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40473471
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question