Use data from mdb table w/ another to display info

[coffeemcadams]

Ok, i wasn't sure how to sum this up in the title or even search this because i'm not sure what it would be called in VB or Access.

What I have is a db with transactions done by about 5 or 6 users. Each user has their own user name and instead of displaying the user name i'd like to link it to another table that has their full name attached to their user name. (i.e. jsmith --> John Smith) and i would like to make a drop down box for a search/filter so that in this drop down box it pulls the full name instead of the user name.

thanks in advance for any help.

[snipered]

In your sql statement

Say something like

mysql = "select * from table1, table2 INNER JOIN table1.code ON table2.code "

Then in your drop down box

while not objrs.eof

combobox1.additem (objrs![Name])
wend

[coffeemcadams]

when you say table1.code and table2.code what does the code stand for?

I tried this and it gives me an error of : Run-time error '91' Object variable or With block variable not set

[anguswalker]

mysql = "select * from table1, table2 INNER JOIN table1.code ON table2.code "

is assuming that table1 is the table with the transactions in and table2 the one with the user information. However the SQL is wrong. It should be

mysql = "select * from table1 INNER JOIN table2 on table1.code = table2.code "

To make it clearer, if your table with the transactions in is tbl_Transactions and the other is tbl_Users, try this

mysql = "select tbl_Transactions.transaction, tbl_users.FullName from tbl_Transactions INNER JOIN tbl_Users ON tbl_Transactions.UserName = tbl_Users.UserName "

[coffeemcadams]

great thank you, now on the drop down itself snipered said to put:

while not objrs.eof
 
combobox1.additem (objrs![Name])
wend

i have no idea how this works on this. i am pretty new to vb and as familiar with as i am with php/mysql so if you need to use that as a reference for explaining feel free.

[Hack]

I'm guessing that objrs is a recordset object created somewhere in the code (not shown) like this

Dim objrs As ADODB.Recordset
Set objrs = New ADODB.Recordset

The recordset is then use to return the results of the SQL query.

Name I'm further guessing is used as an example of the Db table field that stores the actual name you are going after.

[coffeemcadams]

i think i'm doing this completly wrong:

Private Sub Combo1_Change()
Dim objrs As ADODB.Recordset
Set objrs = New ADODB.Recordset
 
objrs.Open "select bcm003.Username, Name.FullName from bcm_mt003 INNER JOIN name ON bcm003.UserName = Name.Username "
 
While Not objrs.EOF
 
combobox1.AddItem (objrs![UserName])
Wend
End Sub

bcm_mt003 is the db
bcm003 is the table that has the transaction in it
Name is the username and full names are

[Hack]

I would take it out of the change event. That is only going to fire if someone types in the combo box or pastes something into it.

Put that code in your Form_Load event and give that a shot.

[coffeemcadams]

ok, now i'm getting an error saying that the context of the .Open is incorrect. am i doing this correctly

Private Sub Form_Load()
 
Dim objrs As ADODB.Recordset
Set objrs = New ADODB.Recordset
 
objrs.Open "select bcm003.Username, Name.FullName from bcm_mt003 INNER JOIN name ON bcm003.UserName = Name.Username "
 
 
Call Connect
RsSearch.Open "select * from bcm003", Cn, 2, 3
RsSearch.MoveFirst
Call DisplayRecord
Grid1.Cols = 0
Grid2.Cols = 0
Grid3.Cols = 0
 
 
'mysql = "select bcm003.Username, Name.FullName from bcm_mt003 INNER JOIN name ON bcm003.UserName = Name.Username "
 
 
End Sub
 
 
 
Private Sub Combo1_Change()
 
While Not objrs.EOF
 
combobox1.AddItem (objrs![UserName])
Wend
End Sub

[anguswalker]

The error might be caused by using "name" as a table name, as it is probably a SQL reserved word. Try enclosing it in square brackets: e.g. INNER JOIN [name] on ...

[anguswalker]

Also you need a second parameter to your objrs.open statement, saying what the connection object is.

[anguswalker]

Try this:

Public ADOCn As ADODB.Connection
Private strConnString As String
Private strSQL As String
 
Private Sub Form_Load()
Dim rs As ADODB.Recordset
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[I]put your databse name and path here[/I];Persist Security Info=False"
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = strConnString
ADOCn.Open strConnString
 
Dim objrs As ADODB.Recordset
Set objrs = New ADODB.Recordset
 
objrs.Open "select bcm003.Username, [Name].FullName from bcm_mt003 INNER JOIN [name] ON bcm003.UserName = [Name].Username ", ADOCn
End Sub
 
 
 
Private Sub Combo1_Change()
 
While Not objrs.EOF
 
combobox1.AddItem (objrs![UserName])
Wend
End Sub

[coffeemcadams]

Now it's giving me an error saying that "Syntax error in JOIN operation"

[anguswalker]

Have you tried your SQL string in the Access query designer?

[coffeemcadams]

Ok, i fixed the SQL string (as dumb as it sounds i didn't realize you could do that, thanks for the tip) I'm new to access.

However, it's still not populating anything into the drop down box.