?
Solved

MS Access - Rowsource syntax

Posted on 2010-01-11
13
Medium Priority
?
525 Views
Last Modified: 2013-11-29
I'm having trouble with the syntax of a rowsource query.  

To illustrate the problem, i built it up in steps.

this works:
SELECT k.id, k.custacctno, k.supplier, k.pricedaterec, k.pricedateexp, k.contractstruct, k.term, k.allinprice, k.ktxmarkup
FROM SUPPLIERpricing AS k;

this works:
SELECT k.id, k.custacctno, k.supplier, k.pricedaterec, k.pricedateexp, k.contractstruct, k.term, k.allinprice, k.ktxmarkup
FROM SUPPLIERpricing AS k
WHERE (((k.opportunityid)=355));

In this case, 355 is just an arbitrary opportunityID.  This shows that a number by itself works.

this does not work:
WHERE (((k.opportunityid)=[Forms]![OpportunityInput].[id]));

I am simply trying to pull the opportunity ID from a text box on my main form.
The main form is opportunityinput, the textbox name is ID. ID is bound to a table called Keystoneopportunities.
The format of that field in the table is autonumber.

Can anyone see where the problem is?
0
Comment
Question by:GTC-KTX
[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
  • 6
  • 2
  • 2
  • +2
13 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 26284823
You need to specify
[Forms]![OpportunityInput].[id].Text

Open in new window

0
 
LVL 85
ID: 26285165
First: What do you mean by "does not work"? Does it throw an error? Does it not throw an error, but does not return any records?

Does your "OpportunityInput" form have a Control or Field named "id"? Are you certain of the spelling?

You state this:

"The main form is opportunityinput, the textbox name is ID. ID is bound to a table called Keystoneopportunities."

I assume you mean that the FORM is bound to a table named KeystoneOpportunities, and that the control named ID is bound to a field in that table.

How are you setting the .RowSource? Are you doing this in VBA?

0
 
LVL 39

Expert Comment

by:thenelson
ID: 26285368
Do not mix up bangs & dots.  Use all bangs:
    [Forms]![OpportunityInput]![id]
or all dots:
    [Forms].[OpportunityInput].[id]

The bang - dot thing is very confusing. It is because Access, like all of MS  products, is a combination of different pieces of software grouped together.  Basically, VBA uses bangs, everything else uses dots. However, it is possible the have SQL in code and VBA code in other places so it is not clear cut. For the first 6 months of using Access, I used the rule, "If a bang doesn't work try a dot, if a dot doesn't work, . ..."

http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx
http://rogersaccessblog.blogspot.com/2009/04/bang-vs-dot-in-dao.html
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:GTC-KTX
ID: 26285481
sameer2010:
when i use your suggestion, it automatically puts brackets around in the following manner, but still doesn't work:

WHERE (((k.opportunityid)=[Forms]![OpportunityInput].[id].[text]));

LSM:
By not work, i mean it does not throw an error, but does not return any records.
The opportunityInput form has a textbox with the name ID.  Yes i am sure of the spelling.
I am setting the rowsource on the data tab of the property sheet, putting the view in SQL view.

Thenelson:
I will try your suggestion and see if that helps!
0
 
LVL 85
ID: 26285850
You should NOT use .Text when referring to Access controls, since in order to use this effectively you must SetFocus to that control
0
 

Author Comment

by:GTC-KTX
ID: 26287403
LSM:
Per your comment, i tried this and still can't get it working:
WHERE (((k.opportunityid)=[Forms].["OpportunityInput"].["id"]));

As info, i also have this code in the afterupdate event of the id textbox.
Private Sub ID_AfterUpdate()
Me.[SUPPLIERpricing subform].Form.ListPRICES.Requery
End Sub

Still having trouble.  Would welcome any other suggestions!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26287441
Try posting your database. To remove private information and reduce the size of the database, do this:
1. Make a copy of your db.
2. If using Access 2007, convert the database to an mdb format.
3. Remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
4. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete. Change the data on the remaining records to remove any confidential information.
5. Import all linked tables so that everything is in the one database
6. Remove any passwords.
7. Disable all Startup options.
8. Compact and repair
9. Move the database to a different folder to make sure it still runs showing the problem
10. Post explicit steps to recreate the issue.
11. Zip (optional) and attach to a message.
... Again, test the DB before posting to make sure that we can open the DB and easily see the issue.
0
 

Author Comment

by:GTC-KTX
ID: 26288536
The attached file is access2007, so i but .text after the filename.  remove this once you download.

1.  a sales opportunity may have several accounts.  Therefore, you will have one sales opportunity in the main keystoneopportunities table, but you could have several accounts for that opportunity that show up in the electricaldata table.

2.  several suppliers may choose to submit pricing for a particular account.

3.  Open supplierpricing subform in design view.  View the code in question, which is in the rowsource for the ListPRICES listbox.  This listbox should show tiems from the supplier pricing table which have an opportunityID that matches whatever sales opportunity you are in (on the main form).

4.  Close this subform.

5.  Open the main form opportunityinput.

6.  navigate with the buttons to opportunity 129

7.  go to the supplier pricing tab, the big listbox should show all items from the supplier pricing table that are for opportunity 129.  Nothing show up in the listbox.
Database2.accdb.txt
0
 
LVL 30

Expert Comment

by:hnasr
ID: 26289135
I can't see the field [Forms].[OpportunityInput].[id] as part of the mentioned form. Check the rowsource for that.

Usually debugging requires isolating controls that function properly and concentrate on the doubted ones.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 26289199
Well! it is there (confusing many fields) with values 114, .....

Run the form, navigate the other OpportunityInput to the record where ID = 124
Run the form SuperPricing Subform.
List box is populated.
0
 

Author Comment

by:GTC-KTX
ID: 26289215
hnasr:
its the textbox labeled "ID" which is directly above "opportunity title" on the opportunityinput form.
0
 

Author Comment

by:GTC-KTX
ID: 26289267
hnasr:
can you explain your last post further?
Are you saying that you got it to work?  If so, it still does not work for me.
0
 

Accepted Solution

by:
GTC-KTX earned 0 total points
ID: 26308262
I solved this problem on my own.  The syntax was correct, but the problem was with the listbox requery.  I had it in the "on update" event of a textbox.  This textbox was autofilling (the user wasn't typing in a number).  Therefore, the requery was never happening.  I moved the requery to the "Current" event of the form and it started working.  Thanks for the comments.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

764 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