SQL statment for different tables???

I have created a Kiosk Information Form where you select a name from and "Owners" table and that name gets recorded onto the "kiosk" table so it shows what owners own what kiosks.

What i have done is created a combo box that you would be able to select from a list of different owners in the database

Dim SQL As String
SQL = "Select * from Owner"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = SQL
Adodc1.Refresh
Do Until Adodc1.Recordset.EOF
Combo3.AddItem Adodc1.Recordset!olastname
Adodc1.Recordset.MoveNext
Loop

So you select the owners lastname from the list. What i want to happen is when i select the specific owner from that list, that his information would get populated into the different textboxes i have setup.

So if i selected Peter from the list. All the information from Peter should appear in the different textboxes.

Comments

  • : I have created a Kiosk Information Form where you select a name from and "Owners" table and that name gets recorded onto the "kiosk" table so it shows what owners own what kiosks.
    :
    : What i have done is created a combo box that you would be able to select from a list of different owners in the database
    :
    : Dim SQL As String
    : SQL = "Select * from Owner"
    : Adodc1.CommandType = adCmdText
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    : Do Until Adodc1.Recordset.EOF
    : Combo3.AddItem Adodc1.Recordset!olastname
    : Adodc1.Recordset.MoveNext
    : Loop
    :
    : So you select the owners lastname from the list. What i want to happen is when i select the specific owner from that list, that his information would get populated into the different textboxes i have setup.
    :
    : So if i selected Peter from the list. All the information from Peter should appear in the different textboxes.
    :

    try-

    'append table name to sql statement- from combobox
    SQL = "Select * from " & Me.Combo1.Text
  • : : I have created a Kiosk Information Form where you select a name from and "Owners" table and that name gets recorded onto the "kiosk" table so it shows what owners own what kiosks.
    : :
    : : What i have done is created a combo box that you would be able to select from a list of different owners in the database
    : :
    : : Dim SQL As String
    : : SQL = "Select * from Owner"
    : : Adodc1.CommandType = adCmdText
    : : Adodc1.RecordSource = SQL
    : : Adodc1.Refresh
    : : Do Until Adodc1.Recordset.EOF
    : : Combo3.AddItem Adodc1.Recordset!olastname
    : : Adodc1.Recordset.MoveNext
    : : Loop
    : :
    : : So you select the owners lastname from the list. What i want to happen is when i select the specific owner from that list, that his information would get populated into the different textboxes i have setup.
    : :
    : : So if i selected Peter from the list. All the information from Peter should appear in the different textboxes.
    : :
    :
    : try-
    :
    : 'append table name to sql statement- from combobox
    : SQL = "Select * from " & Me.Combo1.Text
    :

    That's not what i need because the combobox does not have the title of the table in it, it has a list of thes fields in the Tab owner under the field name "OwnerName" and displays a list of them in the combo box.

    What i want to happen is when you select the name from the combobox , i want it to also pull all the information for that record, not just the OwnerName, and then i want to put the different fields from that record (ie. OwnerAddress, OwnerPhoneNum, OwnerPostalCode) into seperate texboxes. So the ownerAddress, ownerPhoneNum, OwnerPostalCode and OwnerName are all in different textboxes, but appreard there from selecting the owner name from the combobox. Is that making sence at all?

  • [b][red]This message was edited by darthmoobey at 2006-11-22 7:41:39[/red][/b][hr]
    [b][red]This message was edited by darthmoobey at 2006-11-22 7:36:43[/red][/b][hr]
    : : : I have created a Kiosk Information Form where you select a name from and "Owners" table and that name gets recorded onto the "kiosk" table so it shows what owners own what kiosks.
    : : :
    : : : What i have done is created a combo box that you would be able to select from a list of different owners in the database
    : : :
    : : : Dim SQL As String
    : : : SQL = "Select * from Owner"
    : : : Adodc1.CommandType = adCmdText
    : : : Adodc1.RecordSource = SQL
    : : : Adodc1.Refresh
    : : : Do Until Adodc1.Recordset.EOF
    : : : Combo3.AddItem Adodc1.Recordset!olastname
    : : : Adodc1.Recordset.MoveNext
    : : : Loop
    : : :
    : : : So you select the owners lastname from the list. What i want to happen is when i select the specific owner from that list, that his information would get populated into the different textboxes i have setup.
    : : :
    : : : So if i selected Peter from the list. All the information from Peter should appear in the different textboxes.
    : : :
    : :
    : : try-
    : :
    : : 'append table name to sql statement- from combobox
    : : SQL = "Select * from " & Me.Combo1.Text
    : :
    :
    : That's not what i need because the combobox does not have the title of the table in it, it has a list of thes fields in the Tab owner under the field name "OwnerName" and displays a list of them in the combo box.
    :
    : What i want to happen is when you select the name from the combobox , i want it to also pull all the information for that record, not just the OwnerName, and then i want to put the different fields from that record (ie. OwnerAddress, OwnerPhoneNum, OwnerPostalCode) into seperate texboxes. So the ownerAddress, ownerPhoneNum, OwnerPostalCode and OwnerName are all in different textboxes, but appreard there from selecting the owner name from the combobox. Is that making sence at all?
    :
    :


    Hi, what it sounds like you want to do is, make an On Click function for your combo box. After the form loads and places the first name in the combo box. When you click on the combo box and "SELECT" a name it should run the "Combo3_Click()" Command.

    Within the Combo3_Click() command run a query again this time using the where clause.

    [code]
    Private Sub Combo3_Click()

    Dim SQL As String
    SQL = "Select * from Owner where oLastname = " & Combo3.Text
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = SQL
    Adodc1.Refresh
    Text1.Text = Adodc1.Recordset!olastname
    Text2.Text = Adodc1.Recordset!ofirstname
    Text3.Text = Adodc1.Recordset!oaddress

    End Sub
    [/code]

    I'm not sure but something along those lines should help. You may need to close the recordset and reopen it when you change it dynamically at runtime like that, or you could just make another recordset variable. All dependant on what you want or need. Hope that helps.

    You should also be careful, if you have multiple records with the same name you are searching for, you may run into some errors. What you should do is perhaps load the Combo box with Two fields, leaving always the proper text spacing ... as in.

    : : : Dim SQL As String
    : : : SQL = "Select * from Owner"
    : : : Adodc1.CommandType = adCmdText
    : : : Adodc1.RecordSource = SQL
    : : : Adodc1.Refresh
    : : : Do Until Adodc1.Recordset.EOF
    : : : Combo3.AddItem Adodc1.Recordset!oSSN & " " & Adodc1.Recordset!olastname '<- Here it adds the SSN of person and space between name.
    : : : Adodc1.Recordset.MoveNext
    : : : Loop


    Then when you run your second query on click of the combo box, you can set the left function to capture only the SSN.
    SQL = "SELECT * FROM Owner WHERE oSSN = " & Left(Combo3.Text,9)

    Using a unique identifier is always best for querying ONE result. Using only a name can lead to data errors if you have multiple records of that name.

  • [b][red]This message was edited by BigPete85 at 2006-11-24 16:1:1[/red][/b][hr]
    [b][red]This message was edited by BigPete85 at 2006-11-24 16:0:33[/red][/b][hr]
    [b][red]This message was edited by BigPete85 at 2006-11-24 15:55:13[/red][/b][hr]
    : [b][red]This message was edited by darthmoobey at 2006-11-22 7:41:39[/red][/b][hr]
    : [b][red]This message was edited by darthmoobey at 2006-11-22 7:36:43[/red][/b][hr]
    : : : : I have created a Kiosk Information Form where you select a name from and "Owners" table and that name gets recorded onto the "kiosk" table so it shows what owners own what kiosks.
    : : : :
    : : : : What i have done is created a combo box that you would be able to select from a list of different owners in the database
    : : : :
    : : : : Dim SQL As String
    : : : : SQL = "Select * from Owner"
    : : : : Adodc1.CommandType = adCmdText
    : : : : Adodc1.RecordSource = SQL
    : : : : Adodc1.Refresh
    : : : : Do Until Adodc1.Recordset.EOF
    : : : : Combo3.AddItem Adodc1.Recordset!olastname
    : : : : Adodc1.Recordset.MoveNext
    : : : : Loop
    : : : :
    : : : : So you select the owners lastname from the list. What i want to happen is when i select the specific owner from that list, that his information would get populated into the different textboxes i have setup.
    : : : :
    : : : : So if i selected Peter from the list. All the information from Peter should appear in the different textboxes.
    : : : :
    : : :
    : : : try-
    : : :
    : : : 'append table name to sql statement- from combobox
    : : : SQL = "Select * from " & Me.Combo1.Text
    : : :
    : :
    : : That's not what i need because the combobox does not have the title of the table in it, it has a list of thes fields in the Tab owner under the field name "OwnerName" and displays a list of them in the combo box.
    : :
    : : What i want to happen is when you select the name from the combobox , i want it to also pull all the information for that record, not just the OwnerName, and then i want to put the different fields from that record (ie. OwnerAddress, OwnerPhoneNum, OwnerPostalCode) into seperate texboxes. So the ownerAddress, ownerPhoneNum, OwnerPostalCode and OwnerName are all in different textboxes, but appreard there from selecting the owner name from the combobox. Is that making sence at all?
    : :
    : :
    :
    :
    : Hi, what it sounds like you want to do is, make an On Click function for your combo box. After the form loads and places the first name in the combo box. When you click on the combo box and "SELECT" a name it should run the "Combo3_Click()" Command.
    :
    : Within the Combo3_Click() command run a query again this time using the where clause.
    :
    : [code]
    : Private Sub Combo3_Click()
    :
    : Dim SQL As String
    : SQL = "Select * from Owner where oLastname = " & Combo3.Text
    : Adodc1.CommandType = adCmdText
    : Adodc1.RecordSource = SQL
    : Adodc1.Refresh
    : Text1.Text = Adodc1.Recordset!olastname
    : Text2.Text = Adodc1.Recordset!ofirstname
    : Text3.Text = Adodc1.Recordset!oaddress
    :
    : End Sub
    : [/code]
    :
    : I'm not sure but something along those lines should help. You may need to close the recordset and reopen it when you change it dynamically at runtime like that, or you could just make another recordset variable. All dependant on what you want or need. Hope that helps.
    :
    : You should also be careful, if you have multiple records with the same name you are searching for, you may run into some errors. What you should do is perhaps load the Combo box with Two fields, leaving always the proper text spacing ... as in.
    :
    : : : : Dim SQL As String
    : : : : SQL = "Select * from Owner"
    : : : : Adodc1.CommandType = adCmdText
    : : : : Adodc1.RecordSource = SQL
    : : : : Adodc1.Refresh
    : : : : Do Until Adodc1.Recordset.EOF
    : : : : Combo3.AddItem Adodc1.Recordset!oSSN & " " & Adodc1.Recordset!olastname '<- Here it adds the SSN of person and space between name.
    : : : : Adodc1.Recordset.MoveNext
    : : : : Loop
    :
    :
    : Then when you run your second query on click of the combo box, you can set the left function to capture only the SSN.
    : SQL = "SELECT * FROM Owner WHERE oSSN = " & Left(Combo3.Text,9)
    :
    : Using a unique identifier is always best for querying ONE result. Using only a name can lead to data errors if you have multiple records of that name.
    :
    :

    Well the combo box populates with the lastname, but when i select the last name from the combo box and want to display all the information for that record into textboxes I get these two errors.

    "No value given to one or more parameters" and
    "Method of refresh of IAdodc failed"

    I the problem i found that if i manually typed in lastname in the SQL statment it will work, but if you ask the SQL statment to pick the name from the combo box it wont work

    SQL= "Select * FROM Owner Where olastname =" & Combo1.Text =DOESN'T WORK
    SQL= "Select * FROM Owner Where olastname ='Smith'" = WORKS FINE

    So if i were to select Smoth from the combobox i get the error, yet if i go into the code and change the SQL statment to accept 'Smith' it works and populates the textboxes fine.

    Is there any way to make the SQL statment accept the text in the combobox, or any textboxes for that matter???


    ----UPDATE-----
    Figured it out, just had to play around with the SQL statment alittle

    SQL = "Select * FROM Owner Where olastname= " & "'" & combo1.text & "'"
    I guess it needed the ' characters in the SQL statment to read it properly. Thanks for the info and help guys, much appreciated.









Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories