John,
I'm sorry i have never exercised this before, therefore i don't know the method how to HALT.
I tried this with simply opening the code and clicking at left vertical bar, and it highlighted complete code with red color also got circles there.
Then i closed the code window, closed the form which was in design mode-saved and closed.
Then.opened the form and vba code window opened, it was showing:
O Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID,
O ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
O "WHERE Left(cboReceiptsSubID,3) = '" & Me.cboReceiptsID & "'"
1st two circles red
All text background yellow
3rd circle before "WHERE......
also red but having yellow right arrow
Then in immediate window typed
?Me.cboReceiptsSubID.RowSource pressed ENTER
and got
SELECT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description FROM ReceiptSubID ORDER BY ReceiptSubID.ReceiptsSubID;
I then closed this code window, and on the form when i clicked on drop down arrow of "cboReceiptsSubID" and it showed complete list, not against "cboReceiptsID" selected.
~~~~~~~~~~~~~~
Please don't be annoyed, as i admitted earlier i have no idea how to HALT, i explained what i did.
Could you please guide how to do it.
We have less knowledge and experience, that is why we seek help and guidance from the group.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
I told you to halt AFTER the assignment statement. That result has no WHERE clause that should be created by the assignment to cboReceiptsSubID.RowSource.
The point of this exercise is to find out what the code is building and then test it in a query to see if you're getting the results you expect.
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Aug 1, 2017, at 07:30, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
John Viescas, author
This comes in Immediate Window:
?Me.cboReceiptsSubID.RowSource
SELECT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description FROM ReceiptSubID ORDER BY ReceiptSubID.ReceiptsSubID;
Now you said copy this Sql in a new query and see what happens, but you did not give idea from where this new query to be run?
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
All I can suggest is you put a halt on the statement following the Me.cboReceiptsSubID.RowSource =
When the code halts, open the Immediate Window (CTRL+G), and then type:
?Me.cboReceiptsSubID.RowSource
.. and press Enter.
Copy that SQL into a new query and run it to see if it makes sense.
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Jul 30, 2017, at 02:58, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
John Viescas, author
Yes, the list is empty when i drop it down and it is only one row. Moreover i observed that it is like a single box it has no grayed separating line for two fields, although cboReceiptsSubID has bound column 1 and column count is 2, Column Widths are 1cm;4cm
John, In table "ReceiptID" field ReceiptsID at present text data is:
1.1
1.2
And in table "ReceiptSubID" field ReceiptsSubID at present text data against (1.1) is:
1.1.1
1.1.2
1.1.3
1.1.4
1.1.5
1.1.6
We are writing:
"WHERE Left(cboReceiptsSubID,3) = '" & Me.cboReceiptsID & "'"
For Left 3 assuming 1.1 does this dot is been counted, i mean one dot one and we are putting WHERE condition Left 3
Is it correct ?
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
It will still be blank until you assign a value. Is the list empty when you drop it down?
Note that using filtered combo boxes like this on a Continuous form results in display anomalies. Although you can see multiple rows, there is only one current row and one filter value, so any rows that don't match the filter will be blank. In my Inside Out books, I describe a work-around using a text box on top of the second combo box to keep the display from going blank on other than the current row.
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Jul 29, 2017, at 22:33, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
It was my mistake i could not find the spelling mistake:
Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
"WHERE Left(cboReceiptsSubID,3) = '" & Me.cboRecieptsID & "'"
It should have been '" & Me.cboReceiptsID & "'"
Now after correcting the spelling, i don't get the error Member not found, but cboReceiptsSubID is showing blank after update.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
I just noticed that you said cboReceiptsID is text. The code should be:
Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
"WHERE Left(cboReceiptsSubID,3) = '" & Me.cboRecieptsID & "'"
If you're getting member not found, then the name of your combo box must not be "cboReceiptsID". When you type the Me. - Access should show you a list of the members of "Me" - the form object. If you don't see cboReceiptsID on that list, then that's your problem.
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Jul 29, 2017, at 21:03, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Duane,
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
Sorry that i should have addressed you on your reply. I by mistake addressed it to John.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
John,
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
John Viescas, author
Option Explicit was already set on this form, and under Debug Compile SacAct is greyed.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Khalid,
This could just be a simple spelling error that should have been found by setting Option Explicit and compiling your code.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, July 29, 2017 12:09 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Populating a combo box
Sent: Saturday, July 29, 2017 12:09 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Populating a combo box
John,
Getting error:
Method or data member not found
Highlighting
Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
"WHERE Left(cboReceiptsSubID,3) = " & Me.cboRecieptsID
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
You're passing cboReceiptsID to the query engine, and it has no clue what that is. You have to pass the value. Assuming ReceiptsID is a number, do this:
Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
"WHERE Left(cboReceiptsSubID,3) = " & Me.cboRecieptsID
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links! |
On Jul 29, 2017, at 18:38, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi all,
On my Continuous Form "Receipts" I have a Combo Box "cboReceiptsID" with Control Source "ReceiptsID" and Row Source:
SELECT ReceiptID.ReceiptsID, ReceiptID.ReceiptID_Description
FROM ReceiptID
ORDER BY ReceiptID.ReceiptsID;
ReceiptsID is Text field
I need after update of "cboReceiptsID" next Combo Box "cboReceiptsSubID" should populate with its Left 3 of ReceiptsID
cboReceiptsSubID is also Text Field
"cboReceiptsSubID" --> Control Source "ReceiptsSubID"
Row Source:
SELECT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description
FROM ReceiptSubID
ORDER BY ReceiptSubID.ReceiptsSubID;
I am struggling with following code and getting nothing on combo box "cboReceiptsSubID"
Private Sub cboReceiptsID_AfterUpdate()
Me.cboReceiptsSubID.RowSource = "SELECT DISTINCT ReceiptSubID.ReceiptsSubID, ReceiptSubID.ReceiptsSubID_Description " & _
"FROM ReceiptSubID " & _
"WHERE Left(cboReceiptsSubID,3) = cboReceiptsID"
End Sub
May be I'm totally wrong, that is why need help please.
Regards,
Khalid
__._,_.___
Posted by: khalidtanweerburrah@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (15) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar