Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

MS Access - Rowsource syntax

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
GTC-KTX
Asked:
GTC-KTX
  • 6
  • 2
  • 2
  • +2
1 Solution
 
sameer2010Commented:
You need to specify
[Forms]![OpportunityInput].[id].Text

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
thenelsonCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
GTC-KTXAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should NOT use .Text when referring to Access controls, since in order to use this effectively you must SetFocus to that control
0
 
GTC-KTXAuthor Commented:
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
 
thenelsonCommented:
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
 
GTC-KTXAuthor Commented:
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
 
hnasrCommented:
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
 
hnasrCommented:
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
 
GTC-KTXAuthor Commented:
hnasr:
its the textbox labeled "ID" which is directly above "opportunity title" on the opportunityinput form.
0
 
GTC-KTXAuthor Commented:
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
 
GTC-KTXAuthor Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now