combo box but inputs the wrong value.

[oriondcv]

hi, please help me on this issue that im facing... I have created a filtered combo box in ms access. It works but the value it inputs in the database is the primary key value like in my program (lngMyID =1) . What i would like to have is the value that 1 represents username.

here is my source code

Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String
Dim sFunctional As String
Dim sFunctionMade As Variant
 
 
sManagerSource = "SELECT [tblSubCategory].[lngManagerID]," & _
                 "[tblSubCategory].[lngStoreID]," & _
                 "[tblSubCategory].[strSubCategory] " & _
                   "FROM tblSubCategory " & _
                   "WHERE [lngStoreID] = " & Me.cboStore.Value
 
 
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
 
 
End Sub
 

btw this is in reference to this link

http://www.databasedev.co.uk/filter_combo_boxes.html

[Scott Price]

In the properties dialog box for the combobox, check which column is bound. This is the value that will be entered in the database.

So in your SELECT statement, you choose three items, set the bound column to 2, and the second item is what will be entered in the db when selected from the combo box.

As for wanting the UserID/Username to be entered by a choice from what you posted, there's a lot more information that we need in order to understand what you want!

In what you posted, there is no reference to a username anywhere in the Select statement that becomes the combo box row source.

We need a little clearer idea of what you are trying to accomplish.

Regards,
Scott

[oriondcv]

thanks, what im trying to achieve is to have three combo boxes; first combo box then the second one is dependent on the value of the first and then third one is dependent on the second one.

i got the idea from this website.

http://accessprogramme r.blogspot.com/2005/08/filter-combobox-based-on-another.html

but this one specifies 2 combo boxes. Unfortunately, the one im working on what to have 3 filtered combox and each are dependent on each other.

here are my queries:

first combo box:

SELECT DISTINCT [tblTasks].[strCategory]
FROM tblTasks;

second combo box:

SELECT DISTINCT [tblTasks].[strCategory], [tblTasks].[strSubCategory]
FROM tblTasks
WHERE ((([tblTasks].[strCategory])=[forms]![frm_Projects_an d_Tasks].[cboCategory].value));


third combo box

SELECT DISTINCT [tblTasks].[strSubCategory], [tblTasks].[strProductType]
FROM tblTasks
WHERE ((([tblTasks].[strSubCategory])=[forms]![frm_Projects_an d_Tasks].[cboSubCat].value));


im having trouble in displaying the third value. cause it is just blank according to my research there is something wrong in the WHERE statement. I have tried to evaluate this but with my little knowledge on the SQL side; im having difficulties on this one.

pls. advice on this and thanks in advance.

[Scott Price]

Are you using text fields? For example, strSubCategory is a string value?

If so, try changing your cboSubCat.Value to cboSubCat.Text

Regards,
Scott

[oriondcv]

Now the value keeps on displaying the first value if you choose other option it is still the same 1st value..

Im now trying a different way to input data using this link

http://www.databasedev .co.uk/filter_combo_bo xes.html

I have completed the 3rd combo box when I check with the values in the database it displays the number reference in the database [e.g. lngID ]

lngID = 1 (primary number) = Client

What i need is the Client to be input instead of the other number..

Private Sub cboManager_Afte rUpdate()

Dim sManagerSource1 As String

sManagerSource1 = "SELECT [tblProduct_Type].[lngManagerID]," & _
"[tblProduct_Type].[lngStoredID]," & _
"[tblProduct_Type].[strProductType] " & _
"FROM tblProduct_Type " & _
"WHERE [lngStoredID] = " & Me.cboManager.V alue


Me.cboProduct.R owSource = sManagerSource1
Me.cboProduct.R equery


End Sub


Private Sub cboStore_AfterU pdate()
Dim sManagerSource As String
Dim sFunctional As String
Dim sFunctionMade As Variant


sManagerSource = "SELECT [tblSubCategory].[lngManagerID]," & _
"[tblSubCategory].[lngStoreID]," & _
"[tblSubCategory].[strSubCategory] " & _
"FROM tblSubCategory " & _
"WHERE [lngStoreID] = " & Me.cboStore.Val ue


Me.cboStore.Val ue = sFunctionMade

sFunctionMade = DLookup("strCat egory", "tblCategor y", "[lngStoreID]= cboStore")
MsgBox ": " & cboStore



Me.cboManager.R owSource = sManagerSource
Me.cboManager.R equery


End Sub

Im really trying different ways but to no avail. Thanks again :)

[Scott Price]

I can see that you are trying, and that is commendable!

Let's stop trying new things for the moment so we can both get on the same page.

You say you want Client to be entered in a table after clicking on the combo box?

Is Client a text/string value or a number value? How is it related to the rest of the information? Is it a field name?

With your three combo boxes, give me an idea of what you are trying to accomplish. What is the flow of the choices... I.e. you choose something in combo box1 then expect what to show in combo box2, after choosing something in combo box2 what do you expect from combo box3?

Regards,
Scott

[oriondcv]

thanks...

what im trying to accomplish is that when you select a value on the like in my scenario.

1st combo box
2nd combo box will depend on the value that was selected on the first combo box
3rd combo box will depend on the value that was selected on the 2nd combo box.

the declaration for the 1st table

lngStoreID = autonumber
strCategory = text

2nd

lngManagerID = autonumber
lngStoreID = number
lngStoredID = number
strSubCategory

3rd

lngManagerID = autonumber
lngStoredID = number
strProductType = text

the ones in bold are primary keys


what happens is that the value when selecting in the combo box is like

Category = Client, Thin Client
SubCategory = Troubleshooting , etc
ProductType = OS Matters

it capture the details as what is seen in the form. But when you check within the database. It comes as

Category = 1, 21
SubCat = 2
ProductType = 3

The Code im using is:

Private Sub cboManager_Afte rUpdate()

Dim sManagerSource1 As String

sManagerSource1 = "SELECT [tblProduct_Type].[lngManagerID]," & _
"[tblProduct_Type].[lngStoredID]," & _
"[tblProduct_Type].[strProductType] " & _
"FROM tblProduct_Type " & _
"WHERE [lngStoredID] = " & Me.cboManager.V alue


Me.cboProduct.R owSource = sManagerSource1
Me.cboProduct.R equery


End Sub


Private Sub cboStore_AfterU pdate()
Dim sManagerSource As String
Dim sFunctional As String
Dim sFunctionMade As Variant


sManagerSource = "SELECT [tblSubCategory].[lngManagerID]," & _
"[tblSubCategory].[lngStoreID]," & _
"[tblSubCategory].[strSubCategory] " & _
"FROM tblSubCategory " & _
"WHERE [lngStoreID] = " & Me.cboStore.Val ue


Me.cboStore.Val ue = sFunctionMade

sFunctionMade = DLookup("strCat egory", "tblCategor y", "[lngStoreID]= cboStore")
MsgBox ": " & cboStore

Me.cboManager.R owSource = sManagerSource
Me.cboManager.R equery


End Sub

Im trying to have the details in the database as what is being selected in the combo box :)

Thank you very much

[Scott Price]

In other words, right now you are seeing a number stored in the table, and instead you want to see the text associated with that number?

Regards,
Scott

[oriondcv]

yes, it will be hard for us to check one by one on the numbers...

because the combo box seems to work just the output on the database is kinda wrong...

[Scott Price]

yes, it will be hard for us to check one by one on the numbers...

because the combo box seems to work just the output on the database is kinda wrong...

The combo box setup is actually correct. It is intended to store a foreign key id or primary key id in a table.

From that table you create a query to pull the data (including text) that you wish to see. To do this you just include two tables in the query, the main one with main data, and the secondary one with static data.

I'll give you an example from one of my databases of how this works:

I have a Recipe table with this structure:

Name: tblRecipe
RecipeID AutoNumber Primary Key (PK)
RecipeDesc Text
RecipeName Text
RecipeTypeID Number Foreign Key (FK)

Second table: Name tblRecipeType
RecipeTypeID AutoNumber PK
RecipeType Text

You will notice that in my main table I'm storing a Number that links to my second table's primary key. This is called a foreign key.

In the table tblRecipeType I have this data: Dessert, Main, Breakfast, etc...

Now I create a new recipe, and through a combo box choose Dessert for the recipe type. This stores the number value 2 in my tblRecipe. How do I know what a 2 means?

In a query I do this:

SELECT tblRecipe.RecipeID, tblRecipe.RecipeName, tblRecipe.RecipeTypeID, tblRecipeType.RecipeType FROM tblRecipe Inner Join tblRecipe ON tblRecipe.RecipeTypeID = tblRecipeType.RecipeTypeID WHERE tblRecipe.RecipeID = Forms!frmRecipeDetail!cboRecipe

When I run this query I get the RecipeID, Recipe Name, Recipe Type ID and Recipe Type (text name) from the chosen Recipe on my form frmRecipeDetail with a combo box called cboRecipe.

Now... Take a good look at your database, and see how the structure is either the same as what I'm referring to, or different than. Then read this tutorial carefully: Database Normalisation and Table Structures

I suspect what is really happening is that you are trying to apply a properly designed combo box setup to an improperly designed database, or are just not understanding how to use the design to display what you want to see.

Regards,
Scott