Alternative to bound columns please help

[jas1159]

Hi, If possible i am looking for some help regarding an access database I am trying to put together.

I am completely new to access so I appologies if the questions asked are abit daft...

My goal is to return a value input from a form into my database however it is not as simple as using a bound column as it stops my filters from working and i am unable to repair the filter sue to being a novice

I have followed an online tutorial that has tought me how to create 2 combo box's where cmb1 filters cmb2. This has worked brilliantly and results in an email address being available for the person selected in cmbo1.

The method i followed has resulted in my database reading (for example)person 8 where i would like the name of the person.

I am using this after event for cmb1:

Private Sub cboSupplierName_AfterUpdate()
    Dim sSupplierEmailSource As String
    
    sSupplierEmailSource = "SELECT [tblSupplierEmail].[SupplierEmailID], [tblSupplierEmail].[SupplierNameID], [tblSupplierEmail].[strSupplierEmail] " & _
                        "FROM tblSupplierEmail " & _
                        "WHERE [SupplierNameID] = " & Me.cboSupplierName.Value
    Me.cboSupplierEmail.RowSource = sSupplierEmailSource
    Me.cboSupplierEmail.Requery
 
End Sub

It has also resulted in me having 3 databases

db1 = supplier emails against ID
db2 = supplier names against ID

Then a query that brings both name and email together...

The two combo boxes are then part of a form that completes a separate database, this is the main database I would like to store all my information the other 2 dataabses are simply for the filter.

I know this method is not perfect but it is a blend between what i want, how the tutorial created multiple databases and what i can achieve with my knowledge. If you can help make my database return the name and not the ID number i would greatly appreciate your time

Thanks
Jason

 

[JamesMcS]

Welcome to the forum!

3 databases? You mean tables right?

You can use the Column property to bring backa value from a combo box, other than the bound column. As in

somevaariable = combobox.column(x)

Where x is the column number you want, starting from 0.

 

[jas1159]

Thank you James, This worked perfectly!!

Sorry I did mean tables :/ lol

 

[JamesMcS]

No worries, have a good day!

 

[boblarson]

My suggestion is to get the bound fields working. Work with us. We'll help you. But trying to go unbound is taking away a big part of what Access does well for you. Why do extra work when Access can do most of it for you? There are ways to fix your problems. It is a learning experience and we can help.