Need help in VB6 database programming

[Nonie]

Kindly help me. I am new to VB and also in SQL database programming. I am doing a small library system consisting of 3 tables namely: Member which has Mem_Code as primary key, Name etc., Book which has Book_Code, Book_Name etc.and Issue which has both Mem_Code, Name, Book_Code and Book_Name and other records. Data entry to the Issue table, I use a databound DBList to gather all the Mem_Code from the Member table and by a click to the DBList Mem_Code, I transfer
it to a text box. Now I want to do the same to the Name but instead of presenting the user with another DBList box to choose the Name, I want to extract right away the corresponding Name that is associated with what the user has clicked on the Mem_Code and automatically put it to another text box. In this way, I can minimized the user entry error.

For example:

In my Issue table, I have the following:

Mem_Code Name Book_Code Book_Name etc. (other fields)

A100 Michael Jordan 1000 Visual Basic
A200 Andre Agassi 2000 C++

During data entry to Issue table, Mem_Code will go to DBList and when I click on the Mem_Code from the DBList, the Mem_Code - A100 will be transferred to a text box and the corresponding Name - Michael Jordan automatically go to another text box I designated. The same is true for all other records (i.e. A200 transfer to a text box and Andre Agassi to another text box automatically. The same process is applied to Book_Code and Book_Name I have already tried different appproach and looked a lot of text book and surf the internet but I could not find solution.

I have tried this. txtMN.Text = dtaMember.Recordsource = "SELECT Name FROM Mem_Mast WHERE Mem_Code = txtMC.Text" It returns
False. I hope you can provide a solution and correct the code that i presented above. I tried this because it's pretty straightforward but not to VB^ and SQL. I have also tried manipulating Recordset.Fields but it's not giving me the right answer. This may be very easy to you but at this moment, I simply ran out of idea and do not know how to do it.I would greatly appreciate if you could both provide SQL and traditional approach (i.e. Find, Seek, FindFirst etc.).

Thank you very much for your help and your precious time.

Yours,

Nonie

[Tenny]

thanks

[jwshepherd]

txtMN.Text = dtaMember.Recordsource = "SELECT Name FROM Mem_Mast WHERE Mem_Code = txtMC.Text"

needs to be changed to include the variable text

txtMN.Text = dtaMember.Recordsource = "SELECT Name FROM Mem_Mast WHERE ((Mem_Code) = '" & txtMC.Text & "');"

= ' " & txtMC.text & " ' ) ; "

That's equal, single quote,double quote, space, ampersand,space,variable,space,ampersand,space,doublequote,singlequote,close parenthesis,semicolon,doublequote

Hope that helps

[jwshepherd]

post the sub where you are getting the error and we'll see if we can figure it out.

[Nonie]

Hi,

This is the code. Included is the code that you replied. This produces a compile error:

Expected: Expression

The compiler points to the single quote (') in the last equal (=) sign.

Private Sub DBMList_Click()

txtMC.Text = DBMList.BoundText

txtMN.Text = dtaMember.RecordSource = "SELECT Name FROM Mem_Mast WHERE ((Mem_Code) = '" & txtMC.Text & "');" = ' " & txtMC.text & " ' ) ; "

DBMList.Visible = False
End Sub

Thank you.

Nonie

[jwshepherd]

txtMN.Text = dtaMember.RecordSource = "SELECT Name FROM Mem_Mast WHERE ((Mem_Code) = '" & txtMC.Text & "');" = ' " & txtMC.text & " ' ) ; "

needs to be seperated
dtamember.Recordsource = "SELECT Name FROM Mem_Mast WHERE ((Mem_Code) = '" & txtMC.Text & "');" = ' " & txtMC.text & " ' ) ; "
txtMN.Text = dtaMember.RecordSource