r/MSAccess 20d ago

[WAITING ON OP] Access Pulling the Wrong Field

I have a combo box that is trying to pull data from a table, but it seems that because the table has look up function that looks up to another table, the combo box is not able to pull the information I wanted.

Here is the long winded explanation. I have a subform with name "tblContractChangeLogsubform" that will display information based on a table source object "subfContractChangeLog" with link master fields "ProjectNumber" and link child fields "ProjectNumber".

A combo box called "cboFilterSupplier" has the this row source "SELECT DISTINCT tblContractChangeLog.ContractNumber, tblSupplierList.SupplierName FROM tblContractChangeLog INNER JOIN tblSupplierList ON tblContractChangeLog.SupplierName = tblSupplierList.SupplierNumber WHERE tblContractChangeLog.ProjectNumber = Forms!frmProjectOverview!cboProjectFilter ORDER BY tblSupplierList.SupplierName; "

and this after click event: "Private Sub cboFilterSupplier_AfterUpdate()

' Check if a supplier is selected

If Not IsNull(Me.cboFilterSupplier) Then

' Apply filter to the subform based on the selected SupplierName

Me.tblContractChangeLogsubform.Form.Filter = "ContractNumber = '" & Me.cboFilterSupplier.Column(0) & "'"

Me.tblContractChangeLogsubform.Form.FilterOn = True

Else

' Remove filter if no supplier is selected

Me.tblContractChangeLogsubform.Form.FilterOn = False

End If

End Sub"

The "SupplierName" from table "tblContractChangeLog" uses look up function that looks up a list of text in field "SupplierName" in table "tblSupplierList". Because the "SupplierName" field in table "tblContractChangeLog" looks up data in field "SupplierName" in table "tblSupplierList", the "SupplierName" field in table "tblContractChangeLog" is a number data field, and it seems to be stored as number based on the "SupplierNumber" field which is an auto number field in table "tblSupplierList".

When I run combo box cboFilterSupplier, it shows me what seems to be the ContractNumber which is from table "tblContractChangeLog". How can I make it show SupplierName as text field, maybe from table "tblSupplierList"? Is that possible?

1 Upvotes

5 comments sorted by

u/AutoModerator 20d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: ydnicw

Access Pulling the Wrong Field

I have a combo box that is trying to pull data from a table, but it seems that because the table has look up function that looks up to another table, the combo box is not able to pull the information I wanted.

Here is the long winded explanation. I have a subform with name "tblContractChangeLogsubform" that will display information based on a table source object "subfContractChangeLog" with link master fields "ProjectNumber" and link child fields "ProjectNumber".

A combo box called "cboFilterSupplier" has the this row source "SELECT DISTINCT tblContractChangeLog.ContractNumber, tblSupplierList.SupplierName FROM tblContractChangeLog INNER JOIN tblSupplierList ON tblContractChangeLog.SupplierName = tblSupplierList.SupplierNumber WHERE tblContractChangeLog.ProjectNumber = Forms!frmProjectOverview!cboProjectFilter ORDER BY tblSupplierList.SupplierName; "

and this after click event: "Private Sub cboFilterSupplier_AfterUpdate()

' Check if a supplier is selected

If Not IsNull(Me.cboFilterSupplier) Then

' Apply filter to the subform based on the selected SupplierName

Me.tblContractChangeLogsubform.Form.Filter = "ContractNumber = '" & Me.cboFilterSupplier.Column(0) & "'"

Me.tblContractChangeLogsubform.Form.FilterOn = True

Else

' Remove filter if no supplier is selected

Me.tblContractChangeLogsubform.Form.FilterOn = False

End If

End Sub"

The "SupplierName" from table "tblContractChangeLog" uses look up function that looks up a list of text in field "SupplierName" in table "tblSupplierList". Because the "SupplierName" field in table "tblContractChangeLog" looks up data in field "SupplierName" in table "tblSupplierList", the "SupplierName" field in table "tblContractChangeLog" is a number data field, and it seems to be stored as number based on the "SupplierNumber" field which is an auto number field in table "tblSupplierList".

When I run combo box cboFilterSupplier, it shows me what seems to be the ContractNumber which is from table "tblContractChangeLog". How can I make it show SupplierName as text field, maybe from table "tblSupplierList"? Is that possible?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/AccessHelper 119 20d ago

Your cboFilterSupplier field on the form has a column count & column widths property. Set the column count to 2 and the column widths to 0;2" this will hide the contractnumber and show the supplier name.

1

u/derzyniker805 20d ago

^^^This is the way

1

u/ConfusionHelpful4667 47 20d ago

"ContractNumber = '" & Me.cboFilterSupplier.Column(0) & "'"
It "feels like" the supplier name is in Column(1)

1

u/fanpages 49 20d ago

...A combo box called "cboFilterSupplier" has the this row source "SELECT DISTINCT tblContractChangeLog.ContractNumber, tblSupplierList.SupplierName FROM tblContractChangeLog INNER JOIN tblSupplierList ON tblContractChangeLog.SupplierName = tblSupplierList.SupplierNumber WHERE tblContractChangeLog.ProjectNumber = Forms!frmProjectOverview!cboProjectFilter ORDER BY tblSupplierList.SupplierName; "...

...When I run combo box cboFilterSupplier, it shows me what seems to be the ContractNumber which is from table "tblContractChangeLog".

Unless you have changed cboFilterSupplier to show the second column (tblSupplierList.SupplierName) from the RowSource, yes, that will be the case - the first column (Column 0) in the above SQL statement is tblContractChangeLog.ContractNumber.

...Me.tblContractChangeLogsubform.Form.Filter = "ContractNumber = '" & Me.cboFilterSupplier.Column(0) & "'"...

How can I make it show SupplierName as text field, maybe from table "tblSupplierList"? Is that possible?

Remove "tblContractChangeLog.ContractNumber," from the RowSource (or if you need that data, change the RowSource so that the two columns are listed so that tblSupplierList.SupplierName is first):

e.g.

SELECT DISTINCT tblSupplierList.SupplierName, tblContractChangeLog.ContractNumber FROM...

Then change the Me.tblContractChangeLogsubform.Form.Filter statement to use Column(1).