Re: [MS_AccessPros] Populating a combo box

 

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

http://www.viescas.com/

On Jul 30, 2017, at 02:58, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

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

http://www.viescas.com/

On Jul 29, 2017, at 22:33, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

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

http://www.viescas.com/

On Jul 29, 2017, at 21:03, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Duane,

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,

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

 

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

http://www.viescas.com/

John Viescas Consulting www.viescas.com 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: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

---

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.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___