Solved

Access Form

Posted on 2014-11-30
9
403 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
  • 5
  • 2
  • 2
9 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
 

Author Comment

by:clock1
ID: 40472683
Did as you advised, still shows Filtered.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 47

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 47

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now