one query and many forms

Hi all,
I have 1 query and 10 forms. The query is used as a look up for a field 
(StudentID) on all 10 forms.
Per request, I added an unbound combo box on all 10 forms (cbo151). I'd like 
to use the value in this combo box as a parameter for a field in the query.
Is there a way I can grab the value in the combo box from the current open 
form and pass it to the query?
I used Forms!myForm1!cbo151 as the parameter but it needs to change for the 
next form. I'd hate to create 9 more queries.
Is there an easier way?
Thanks,
LGarcia

 


0
LGarcia
6/18/2007 8:05:56 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
624 Views

Similar Articles

[PageSpeed] 11

You can use a function to pass the value to the query
----------------------
Create a function within a module

Function GetStudentId() As Long
GetStudentId = StudentIdParam
End Function
--------------------------
Declare StudentIdParam within the module (not within the function)

Global StudentIdParam As Long

--------------------------
Before you open the query assign the combo value to StudentIdParam

StudentIdParam = Me.[ComboName]
Docmd.OpenQuery "QueryName"

--------------------------
In the query you can refer to the function

Select * From TableName Where StudentId =  GetStudentId()
--------------------------

Note: I declared the student id as long, change the type if you need to.
-- 
Good Luck
BS"D


"LGarcia" wrote:

> Hi all,
> I have 1 query and 10 forms. The query is used as a look up for a field 
> (StudentID) on all 10 forms.
> Per request, I added an unbound combo box on all 10 forms (cbo151). I'd like 
> to use the value in this combo box as a parameter for a field in the query.
> Is there a way I can grab the value in the combo box from the current open 
> form and pass it to the query?
> I used Forms!myForm1!cbo151 as the parameter but it needs to change for the 
> next form. I'd hate to create 9 more queries.
> Is there an easier way?
> Thanks,
> LGarcia
> 
>  
> 
> 
> 
0
Utf
6/18/2007 9:46:00 PM
Thanks! I'll give it a try.


"Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message 
news:E7E47ECC-FEE9-443E-AA91-55558DD20AF2@microsoft.com...
> You can use a function to pass the value to the query
> ----------------------
> Create a function within a module
>
> Function GetStudentId() As Long
> GetStudentId = StudentIdParam
> End Function
> --------------------------
> Declare StudentIdParam within the module (not within the function)
>
> Global StudentIdParam As Long
>
> --------------------------
> Before you open the query assign the combo value to StudentIdParam
>
> StudentIdParam = Me.[ComboName]
> Docmd.OpenQuery "QueryName"
>
> --------------------------
> In the query you can refer to the function
>
> Select * From TableName Where StudentId =  GetStudentId()
> --------------------------
>
> Note: I declared the student id as long, change the type if you need to.
> -- 
> Good Luck
> BS"D
>
>
> "LGarcia" wrote:
>
>> Hi all,
>> I have 1 query and 10 forms. The query is used as a look up for a field
>> (StudentID) on all 10 forms.
>> Per request, I added an unbound combo box on all 10 forms (cbo151). I'd 
>> like
>> to use the value in this combo box as a parameter for a field in the 
>> query.
>> Is there a way I can grab the value in the combo box from the current 
>> open
>> form and pass it to the query?
>> I used Forms!myForm1!cbo151 as the parameter but it needs to change for 
>> the
>> next form. I'd hate to create 9 more queries.
>> Is there an easier way?
>> Thanks,
>> LGarcia
>>
>>
>>
>>
>> 


0
LGarcia
6/19/2007 1:43:08 PM
Reply:

Similar Artilces:

can excel check one column against another
Hi. I have a spreadsheet with columns such as A, B, C, D, filled with numbers. Each row across such as row 1, then row 2, then row 3, etc of numbers is totaled in column E. Countless rows, each of which is totaled per row and then ends with a grand total at bottom of column E. Next comes columns F, G, H, also filled with numbers (but different numbers), which is totaled per row in Column I. At the bottom of Column I is a grand total. Even though arrived at using different sets of numbers, -both grand totals - in columns E and I must match. Sometimes they don't match. Ouch. So here is m...

query syntax error
SELECT [EventsID], [EventName] FROM Events WHERE (([Catagory]=(Event Catagory[EvtCatID]))); The above is what I have in my query builder Events is a fld Datatype autonumber EventName is a fld Datatype text FROM Events iis the Table WHERE Cataory is a fld datatype Number Event Catagory is a tbl EvtDCatID is a fld Datatype autonumber The fld the result is to go in is ChurchService Datatype number Properties combobox tbl query rowsource is the above query builder bound column 1 column count 2 column width 0",1" Iget a syntax error with the where...

why crash in the second method of one interface
Hi, Using one class generated from *.tlb to call 2 methods of one COM. The strange thing is that I got one error of the "First-chance exception in : 0xC0000005: Access Violation" in the second method. I also use ASP web page to call both methods of this COM and both methods seems is OK. ITemplateManage mytemplate; if (!mytemplate.CreateDispatch("comobj.TemplateManage")) { IDE_Error::Report (ComError|1,"ComObj.TemplateManage in FUN_Template::Add()"); return false; } result = mytemplate.Check (templatefilename); decodeparameter=mytemplate.GetInfo (templ...

Positioning 2 User Forms
Hi I hope I explain this correctly - I have user form 1 which on opening fills the monitor screen with everything in its correct place and working OK. Within the same Workbook I have a small user form 2 which is opened by a command button on user form 1. On opening that works perfectly OK. However no matter what I try and do and that includes changing Start Up Position = in the VBA the damn thing always opens in the middle of user form 1 - when the desired position would be in an empty space available on the baseline of user form1. Any help and suggestions would be much appreciated ...

MSR Credit Card Reader adds all the information on one line
Is there a way to get the CC reader to properly seperate the the acct # from the expiration date? When I swipe I currently get everything on the acct # line. Thanks in advance! What type of CC reader(MSR) to you have? Typically this is an issue with simply configuring the MSR. Rob "Steakum" wrote: > Is there a way to get the CC reader to properly seperate the the acct # from > the expiration date? When I swipe I currently get everything on the acct # > line. > > Thanks in advance! Its a IDTECH Minimag USB/Keyboard Interface Do I need to select the Wedge Key...

Duplicate Transactions, One When Pending, One When Posted
I'm sure this has been addressed, but I couldn't seem to find an answer searching: My problem is, a transaction occurs at my bank, and MM2K7 downloads the data for it. Once that transaction moves from "Pending" to "Posted" at my bank, the transaction data changes, so MM downloads it again. Sometimes I get a possible duplicate message from MM, sometimes I don't. What are people doing to handle this situation? Thank you. This is a problem with your bank, not with Money. Eventually they should fix it. Call them. In the meantime VOID the pending transacti...

Worm updated form MS caused problem in Outlook 2002
After I download updated from MS that supposed to fix the worm blast I CANNOT NO LONGER CREATE/REPLY/REPLY ALL IN MY OUTLOOK 2002. Pls assist If yr critical updates were upto date this patch would have allready been installed in the update of July. Have you tried Detect/Repair David "wcw" <wcw@access-solution.net> wrote in message news:06db01c365f0$25c43d40$a401280a@phx.gbl... > After I download updated from MS that supposed to fix the > worm blast I CANNOT NO LONGER CREATE/REPLY/REPLY ALL IN > MY OUTLOOK 2002. > > Pls assist ...

Combo Box Query Not Working
I’m new to access but learning quickly. I’m trying to create a data base for our “time change” items on out plant equipment. Such as; Furnace (asset) Clutch (part) Installed date (date installed) Replacement date (set date in the future e.g. 6 months from install date) I already have a table with the columns named asset, part, installed date and replacement date. It’s called Table 1. All dates are formatted to medium date format and input mask the same. I also have a table with IntervalTitle, Interval and IntervalIncrement set as follows: IntervalTitle Inte...

grid off on one page
Is it possible to turn the grid off of only one page? thanks On Thu, 14 Feb 2008 10:37:11 -0600, "greg" <iuouh@ghfhg.com> wrote: >Is it possible to turn the grid off of only one page? Not really as it is a document wide setting.... however, there is workaround open the page shapesheet menu window -> Show shapesheet and set XGridSpacing and YGridSpacing to a large value. -- Regards, Paul Herber, Sandrila Ltd. SSADM for Visio http://www.saadm.sandrila.co.uk/ On Thu, 14 Feb 2008 16:47:53 +0000, Paul Herber <SubstituteMyFirstNameHere@pherber.com> wro...

One to One relationship
I have an Events entity that is an offshoot from the Contacts entity. Basically, it tracks what events each contact has been invited to, and is used mainly by the Events Coordinator. I've set it up as a One (Contact) to Many (Events) relationship. However, I really only want one event form for each contact form. The reason I've set up a different entity rather than just adding a tab is that access to this entity needs to be tightly controlled. Obviously, if many events forms are created for the one contact, it would be very difficult to tell which one is accurate etc. Is it possibl...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

route inbound email from ip to one folder
Is there a way to route any incoming email from a certain IP address to a single folder? Our company is receiving emails from a source that we do not wish to receive, and would like these email to be directed to a singe folder rather than blocking them. Any ideas? Thanks. you could send them to the turf dir... http://support.microsoft.com/default.aspx?scid=kb;en-us;245465 "daine" <anonymous@discussions.microsoft.com> wrote in message news:11d501c4263d$f4d05110$a101280a@phx.gbl... > Is there a way to route any incoming email from a certain > IP address to a single ...

Crosstab Query results/ Sum
I created a crosstab query that outputs sales by each month (month as a column header). I am creating a second query to retrieve sums of the months I am interested. Let's say the sales is as follows Month1 Month2 Month3 $200 $100 $300 Here is the formula I am using: Last3Months: Month1 + Month2 + Month3 Instead of getting the sum which shoudl be $600, it looks like the formula is concatenating the values resuting in 200100300 i guess i can just sum it up in Excel but wanted to see if i can do it in Access. Based on what you posted, it looks l...

One client (Server 2k8-R2/HyperV) will not sync with DPM
We have been running DPM successfully for about a year now without any major problems. Recently we added a new server - a fairly robust PowerEdge that will be housing 4 HyperV VMs. For whatever reason, I can't get anything but the HyperV "Initial Store" to sync correctly. I thought initially it might be a problem with the HyperV machines, but it also fails to back up a small directory of files on the host drive. The oddest part about this is I really don't get an error. It will try an initial sync and run for a day or two and show very minimal data tr...

To view, specifically Units in one report
I have report with a Reporting Tree (9 Unit), when the report is generated, I select only two unit, but I need that the drill down show one report with these unit only If you want multiple units in a single report you will need to use the column layout with unit restrictions on multiple columns Leslie "Pedro" wrote: > I have report with a Reporting Tree (9 Unit), when the report is generated, I > select only two unit, but I need that the drill down show one report with > these unit only Leslie; Each Column layout have the unit restrictions Thanks :-) "Lesl...

A Dynamic Query Worksheet?
I have the unfortunate task of converting a SQL Server database into stand-alone Excel file that will store all the data, as well as act a a "front-end' for browsing the data. I'd like to have an opening page of many different hyperlinks whic would send the user to a second worksheet and also pass a variable. The second worksheet would list the results of a query that would us the variable as a parameter and would get its data from a huge table o a 3rd Spreadsheet. Is this idea possible? If so, I'd appreciate any ideas, tips, hints o how to get this done. Thanks -- Messa...

Exchange 5.5 - One Organization
Hello - I am having a problem that I would like some more information on. I have One Organization (5.5) and 16 Sites (5.5) and would like to know why I can't stop the other sites from sending mail through me. If I can, I would like to know how. Thanks for any help. what do you mean by "sending mail through me"? What mail? Internet email? Does each site have its own Internet Mail Service? Or is your site kind of the "hub" where all Internet email comes in and goes out? -- Susan Conkey [MVP] "dave.mudgett" <davemudgett@discussions.microsoft....

See more than one tab at a time
I would like to be able to see more than one Tab at a time in Excel. Currently I'm able to see two Tabs from different worksheets, but not two or more tabs from the same worksheet On Sat, 28 Aug 2004 14:57:03 -0700, "rocky59" <rocky59@discussions.microsoft.com> wrote: >I would like to be able to see more than one Tab at a time in Excel. >Currently I'm able to see two Tabs from different worksheets, but not two or >more tabs from the same worksheet That would be because each tab represents a single worksheet. Clicking on the tab would move you from where ...

Why Would You Lose Sound On One User
WXP Pro Version 2002 Service Pack 3 with all current updates Dell Pentium(R) D CPU 2.80Ghz 2.79Ghz, 512 MB RAM System has three users all with admin rights - User 1(primary - me), User 2 (wife), and System Admin. (which I made visible on desktop). I (User 1) have lost sound when attempting to play CD's, and other audio files using WMP 11, however, I can still hear system sounds. User 2 and System Admin. still have full sound. System restore does not help ( in this case I only had a restore point from 5 days ago. Any thoughts? -- Skeeter "Old age and ...

Combining two fields into one in a query for a report
The database I am working on is to keep track of parts that are either in their storage area (on a shelf) or on an assembly line. I have two fields, one called Shelf and every part has a shelf assigned to it. The other field is Line and if a part in on an assembly line, there is a number indicating that line, if the part is on it's shelf, the word STOCK appears in the line field. (Has to be this way for the rest of the database to work, so this is not up for question) Now, I need to creat a report to show where all parts are. If they are on a line the result needs to show the l...

Tracing and tracking email on one mailbox
1. I want to use a current copy of the Exchange 2003 database and scrub it for all emails regarding one mailbox. Is there a tool that will assist me in this? 2. How do I tell Exchange 2003 to notify me of any emails transmitted to and from a particular address? And yes, this in conjunction with #1. I want to track one user's mail without using delegate or adding myself to their Active Directory profile and having to create another profile in Outlook. 1. Have a read through this: http://wm.quest.com/reg/marketing/landing/ediscoveryexchange/. This product is ideally suited for ...

transferring money from one computer to another
Folks, I just got this laptop which I really like, but unfortunately, I can't figure out how to get my MS Money program (not the files-- that's a no-brainer) to my new computer. I downloaded the program and did not get the CD for it. Is there a way to do it? Both of these computers are mine, so I should be able to, but I can't figure it out. Thanks! Govind... There are almost no apps that can be moved from one machine to another these day. Almost all require having the installation routines. This is why I always recommend against "download" installations. &qu...

Forcing Input as Uppercase on User Form
Hi can any one advise me how to amend the following code to ensure :- (a) only two letters are input (b) they are converted to upper case (c) how do you centre the text in a message box header as I have use spaces to achieve this Code used inputVal = TextBox1 Do Until ActiveCell = inputVal ActiveCell.Offset(1, 0).Select If ActiveCell = "" Then MsgBox ("Sorry Not Found - Please Input the first two letter only e.g. RG ") ", vbInformation, " Warning !! " Unload UserForm1 UserForm1.Show End Else End If Loop Excel v...

Monitoring Physical Reads of a Query on sql 2005
I run a sql in the following form to see io performance dbcc freeproccache go dbcc dropcleanbuffers go set statistics io on select ..... set statistics io off After the first attempt, I am showing on the main table (200,000 rows) 33 physical reads. A minute later I am running the same query, 51 physical reads. As it is, this is an oltp system, and data on these tables doesn't get updated often. The execution plan didn't change. What am I missing here? Thanks in advance Hard to say but you have easily had some page splits which would account for more...

One-to-one relationship
I am importing a DB from Filemaker with info about students (names, address, etc.). There is just one table and because it has been used for many years, it cannot be changed. I have created another table for additional student enrollment information. I have created a one-to-one relationship between the two tables using an autonumber primary key in the student table and a number primary key in the enrollment table. I have created two forms to add information to the tables. All new students are added via the old student form first. When I switch to the new student form, I can...