assigning a unique query for each textbox on a form

I've got a table (tblMain) with the following (relevant) fields:

tblMain
    .FirstName
    .LastName
    .Seat
    .RefNum

I've got a form that has a graphical display of the seats to which the 
people listed in tblMain are assigned.  I'd like to put a text box on each 
seat corresponding to first and last name of the person assigned to that seat 
(stored in tblMain.Seat).  The same database is used for various seatings so 
I'd like only those associated with the RefNum at hand to be called.  I can 
write a simple enough query which I can call from VBA and which relies on 
atext box on the form that holds the refNum (txtRefNum) to accomplish this:

stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat, 
tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1") AND 
((tblMain.RefNum)='" & Me.txtRefNum & "'));"

But I just don't know how to make use of this once I've done it.  If it was 
a combo box I would tell it to set RowSource to this query... but I don't 
think I can do that with Control Source, right? So... I imagine there's an 
expression I can write for the Control Source but I'm still unclear how to 
rewrite queries as expressions.  Is that how to proceed?  If so, how do I do 
that?  If not, what should I do instead?  Thanks!
0
Utf
1/7/2008 6:57:05 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
505 Views

Similar Articles

[PageSpeed] 11

Have you tried using the control source?  I think it would work.
-- 
Dave Hargis, Microsoft Access MVP


"ArielZusya" wrote:

> I've got a table (tblMain) with the following (relevant) fields:
> 
> tblMain
>     .FirstName
>     .LastName
>     .Seat
>     .RefNum
> 
> I've got a form that has a graphical display of the seats to which the 
> people listed in tblMain are assigned.  I'd like to put a text box on each 
> seat corresponding to first and last name of the person assigned to that seat 
> (stored in tblMain.Seat).  The same database is used for various seatings so 
> I'd like only those associated with the RefNum at hand to be called.  I can 
> write a simple enough query which I can call from VBA and which relies on 
> atext box on the form that holds the refNum (txtRefNum) to accomplish this:
> 
> stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat, 
> tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1") AND 
> ((tblMain.RefNum)='" & Me.txtRefNum & "'));"
> 
> But I just don't know how to make use of this once I've done it.  If it was 
> a combo box I would tell it to set RowSource to this query... but I don't 
> think I can do that with Control Source, right? So... I imagine there's an 
> expression I can write for the Control Source but I'm still unclear how to 
> rewrite queries as expressions.  Is that how to proceed?  If so, how do I do 
> that?  If not, what should I do instead?  Thanks!
0
Utf
1/7/2008 8:01:02 PM
I did... it returned one of those #Name? errors instead of populating 
propery.  The way I did that was in OnCurrent for the form.  I tried the 
following:

'**START CODE***

Private Sub Form_Current()
    Dim stSQLHolder As String
    stSQLHolder = "SELECT tblMain.FirstName FROM tblMain WHERE 
(((tblMain.Seat)=1) AND ((tblMain.RefNum)='" & Me.txtRefNum & "'));"
    Me.txtSeat1First.ControlSource = stSQLHolder
End Sub

'***END CODE***

I also tried:

stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat, 
tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1) AND 
((tblMain.RefNum)='" & Me.txtRefNum & "'));"

and the above two with '" & Me.txtRefNum & "' replaced with '206CBR2985' to 
see if hardcoding a RefNum would help.  No luck.

I think part of the problem is that I want to query tblMain for FirstName, 
LastName, Seat, and RefNum and then only call to one of those... so I was 
thinking I could change the form's control source from tblMain to a query and 
then in the query I could have subqueries for each seat... something like 
SELECt tblMain.Seat As Seat1 from tblMain WHERE tblMain.Seat = 1 but I'm not 
sure if that's the way to do this nor am I sure about how this is going to 
work or if there is a way to loop through this so I don't have to write a 
subquery for each seat or what.  Anyhow... I'm open to any guidance you might 
have for me.  Thanks for your help!

Just in case this was about 
"Klatuu" wrote:

> Have you tried using the control source?  I think it would work.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "ArielZusya" wrote:
> 
> > I've got a table (tblMain) with the following (relevant) fields:
> > 
> > tblMain
> >     .FirstName
> >     .LastName
> >     .Seat
> >     .RefNum
> > 
> > I've got a form that has a graphical display of the seats to which the 
> > people listed in tblMain are assigned.  I'd like to put a text box on each 
> > seat corresponding to first and last name of the person assigned to that seat 
> > (stored in tblMain.Seat).  The same database is used for various seatings so 
> > I'd like only those associated with the RefNum at hand to be called.  I can 
> > write a simple enough query which I can call from VBA and which relies on 
> > atext box on the form that holds the refNum (txtRefNum) to accomplish this:
> > 
> > stSQLHolder = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat, 
> > tblMain.RefNum FROM tblMain WHERE (((tblMain.Seat)=1") AND 
> > ((tblMain.RefNum)='" & Me.txtRefNum & "'));"
> > 
> > But I just don't know how to make use of this once I've done it.  If it was 
> > a combo box I would tell it to set RowSource to this query... but I don't 
> > think I can do that with Control Source, right? So... I imagine there's an 
> > expression I can write for the Control Source but I'm still unclear how to 
> > rewrite queries as expressions.  Is that how to proceed?  If so, how do I do 
> > that?  If not, what should I do instead?  Thanks!
0
Utf
1/7/2008 8:23:01 PM
Reply:

Similar Artilces:

faxing form
Is there a way to fax the form page via the fax modem on my computer? Create a report based on your form then fax the resulting report. Take a look at http://support.microsoft.com/Default.aspx?kbid=231797 hopefully it is what you are looking for. -- Hope this helps, Daniel Pineault If this post was helpful, please rate it by using the vote buttons. "He cries for help" wrote: > Is there a way to fax the form page via the fax modem on my computer? "He cries for help" <Hecriesforhelp@discussions.microsoft.com> wrote in message news:252D07EA-5886-4B0C-99...

reference to master form
Hello there I have master form and inside there is subform. If an event procedure uccur on subform, how can i get refence to the master form in order to change there things when the name of the master form is diffrent always? Roy Look in Access HELP for the "Parent." property. Your code will look something like: Me.Parent.... from within the subform. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Roy Goldhammer" <roy@ho...

How can I refresh the master form from a subform?
Hello. On the After Update of the subform I am trying to refresh the Master form. I have tried things like Docmd.requery and Me.Refresh, but not sure what I am doing. Can you help me with this? Thanks. Iram Iram wrote: >On the After Update of the subform I am trying to refresh the Master form. I >have tried things like Docmd.requery and Me.Refresh, but not sure what I am >doing. Try using: Parent.Requery -- Marsh MVP [MS Access] ...

Modal Form from a child form
Hi, I have a little problem, I have a MDI form (Main Menu), this form open a child form (A), and I need to open a form B as modal form from form A. I can to this with showDialog(), but it open form B outside MDI form (Main Menu) and it is showed on the windows bar as another application opened. So the question is, there are any way to open a modal form from a child form inside a MDI form? Thanks El Alquimista wrote: > Hi, I have a little problem, I have a MDI form (Main Menu), this form > open a child form (A), and I need to open a form B as modal form from > form A. I c...

Data source for PivotTable-Form in ACCESS 2000
Hi, this drives me crazy, 4 years ago I defined in an ACCESS 2000 application a "PivotTable-Form". The resulting EXCEL table inclusive the "Data refresh" works perfect. I now want to update/change the query for the "Data Source" but cannot find which query is behind the "PivotTable-Form" or behind the resulting EXCEL spreadsheet. When editing the properties for the "PivotTable-Form" or the EXCEL spreadsheet the "Data Source" is always blank. Question: Where does Access 2000 or EXCEL 2000 hide the respective data source (Query)...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

Stop Buttons showing when opening up form
I have a button on my Main Start up page that when click makes these buttons visible, but when I open up my DB they automatically show on start up, is it possible they not be visible till I click ckbHelp.......Thanks for any Help.....Bob Private Sub ckbHelp_Click() If ckbHelp = True Then cmbHelpNewHorse.Visible = True cmbHelpActFinHorse.Visible = True End If If ckbHelp = False Then cmbHelpNewHorse.Visible = False cmbHelpActFinHorse.Visible = False End If End Sub Bob, In design view, set the property pf the *button* Visible=No Regards/Jacob "Bob" <xxx@xx.xx> wrote i...

Bug with controls location and scrollbars in a Windows Form
Hi, I've noticed a bug with WinForm and scrollbars, and I would like to know if there is a workaround ... Bug description : ------------------------ 1) create a win form, and add 3 buttons : * FirstButton : upper left corner of the winform * SecondButton : location = (0 ; 600) * ThirdButton : location = (100, 600) 2) Set : ThirdButton.Visible = false 3) Set the Form size to 100 x 100 ... and set : AutoScroll = true 4) Add the following event handler to the Click event of FirstButton and SecondButton ... private vo...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

Cannot view any form in crm 3.0
We have just installed CRM 3.0 on MS server 2003. I have created an account which has a system administrator role assigned to it. The issue is that when I open CRM web (on the server) when I select account, contact, activities, calendar etc... I get the following error: An error has occured. For more information, contact your system administrator. Now if I open CRM web using an account which has restricted access mode ticked, I don't get any errors, everything opens fine. The problem is that with an restricted access code i can't do much. I have tried assigning different roles ...

Minimizing form
Hi How can I minimize the current form? Thanks Regards DoCmd.Minimize in a command button code or other event. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "John" <John@nospam.infovis.co.uk> wrote in message news:uBcRcE73HHA.3600@TK2MSFTNGP02.phx.gbl... > Hi > > > > How can I minimize the current form? > > > > Thanks > > > > Regards > > ...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...

Form pages
Is it possible to have a two page form and only print the first page or second page when needed.? Depending on your design, you should be able to use the File/Print dialog box selection to print 1 of 2, or 2 of 2 pages. But, forms are made for data entry, and viewing... not printing. Better that you develop a rptPageOne and a rptPageTwo and select either for printing from your form. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." &quo...

How to assign these rights
Hi all, Windows 2003 SP2 server ex: file folder as d:\123\1234\12345 we share 1234 as 1234 We do not want users to write into 1234 folder to create or delelte folders But, we want users to have write access to 12345 to change or edit or save etc there How should I assign these rights? Thank you. You need share the folder 12345 with the corrrect rights ... the problem with you scenario is the combination of Share permissions and NTFS permissions... search in the web topics about this... Exist another way to do your request but it's increase the administrati...

Form Opening with Blank Column(s)
I have created a couple applications that are working well on our remote access server, Access 2003. However, when I have people put the application on there standard desktop on the network, one or more columns appear blank when the form is opened in Access 2003. Any ideas why the data retrieval or function would change between our server addition of Access vs client workstations? -- Message posted via http://www.accessmonster.com I have one user in my organization that is experiencing this problem also. The problem started the week of the 25th of February. The user could not give me a ...

sending a form
Good day, I am trying to create an issue tracker where one can use a form to assign someone from a given list to a task and then notify them of the task by being able to send the person an email containing the form. So far, I have only been able to create a macro that sends the entire database (and the user has to manually enter the email address). Any ideas on how to automate all of this? You can only natively do this in Access 2007. For earlier versions try Easy Survey: http://www.mapilab.com/outlook/easy_survey/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org...

New contact form population
Hi everyone! Here is the problem we are having. When adding a new account and contacts to CRM we start by adding an account. From that account/contact page, we click on "new contact". Previously, this contact form would pop up and many of the fields would be populated. For some reason, some of these fields are not populating. The parent account and several of the address fields remain blank. Any ideas? Have you changed the field mappings in Deployment Manager? I'd start by verifying that there is still a mapping between Accounts and Contacts, and that the fields ...

Adding Contact Person to SOP Other Invoice Form
We would like to add a field to the "Bill to" and "Ship to" addresses that is the "Contact Person" field from the Address ID palette. How do I do this? This is done easily, check out Knowledgebase Article 874176. You will need to add some additional calculated fields and then modify the existing calculated fields. Using the rw_SelectAddrLine user function you can add the following fields (for example) to an address and have it grow and shrink dynamically with no empty lines. Customer Name Contact Person Address1 Address2 Address3 City State Zip Country ...

Editing Password protected form fields in Word 2007
We have created a Word document with a legacy text form field. We inserted a word date field and protected the document allowing Filling in Forms. After protecting the document when we try going to the form field and clicking on back space to delete the entry we get "This is not a valid selection" message box. We have existing word documents in 2003 with similar form fields. We were able to delete the entries and add new data. After converting the documents to 2007 or create new documents in 2007 and try to delete the entries we get the above error. Hi Veera, ...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...