Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2004
    Posts
    43

    Question Unanswered: output sql statement in a textbox?

    hi,
    i want to know how to output the selected record in textboxes

    ex:
    txtaddress = "select address from customers where customerid=txtcustomer"


    but this does not work... help pls

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm not really sure what you mean or what the ultimate goal is here. But...maybe this is what your thinking:


    Me.txtaddress.ControlSource = "select address from customers where customerid=txtcustomer;"



  3. #3
    Join Date
    Jun 2004
    Posts
    43
    tnx sir

    i'm a beginner in access can i know what is the meaning of "me"?

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Me.myTextBoxName.ControlSource

    is the same as

    Forms![myTextBoxName].ControlSource
    Me can only be used though if you are referencing controls on the form which has the current focus. The other method can be used globaly providing the actual form name being accessed is also indicated.


  5. #5
    Join Date
    Jun 2004
    Posts
    43
    Me.txtbalance.ControlSource = "select balance from customer where customerid=combocustomerid;"


    what i want is to show the select result in a textbox
    ex: the select statement output is $200.00 i want to see the $200.00 in txtbalance textbox

    help pls...

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Me.txtbalance.ControlSource = "select balance from customer where customerid=" & combocustomerid & ";"

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jun 2004
    Posts
    43
    i tried the code above but the output in the textbox is "#Name?"
    what does "#Name?" do i need to change some properties? like the control source, should it be unbound?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ahA! now i see customerid=txtcustomer in your first post.

    my code works if your combo returns numbers.

    if your combo is returning text you need:
    "select balance from customer where customerid='" & combocustomerid & "';"

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jun 2004
    Posts
    43
    Private Sub cmbvcustomerid_AfterUpdate()
    Me.txtbalance.ControlSource = "select balance from qrycheque where customerid='" & cmbvcustomerid & "';"
    End Sub


    customerid is text
    balance is currency

    the result in combo box is "#Name?" what does this mean?
    Last edited by oeuf85; 06-13-04 at 13:12.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the important question is the type of your combo bound-column.
    to discover: ask it!

    if isnumber(combocustomerid) then
    msgbox "combocustomerid: " & combocustomerid & " is numeric"
    else
    msgbox "combocustomerid: " & combocustomerid & " is not numeric"
    endif

    you might also want to play with:

    dim ici as integer
    for ici = 0 to combocustomerid.columncount - 1
    if isnumber(combocustomerid) then
    msgbox "combocustomerid.column(ici): " & combocustomerid.column(ici) & " is numeric"
    else
    msgbox "combocustomerid.column(ici): " & combocustomerid.column(ici) & " is NOT numeric"
    endif
    next

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jun 2004
    Posts
    43
    no the combobox is data type is text from a table, user cannot enter values in the combobox just choose and then it will show the balance in the txtbalance...
    this is what i'm asking... how to show the balance in txtbalance when user chooses on the combobox?

    qrycheque:
    customerid
    balance
    accounttype

    cmbvcostumerid
    control source is: customerid
    row sourceELECT qrycheque.customerid FROM qrycheque;

    txtbalance
    control source: balance
    Last edited by oeuf85; 06-13-04 at 13:43.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try another approach:

    in your combo's _AfterUpdate() event:

    textbalance = DLOOKUP("balance", "qrycheque", "customerid = '" & cmbvcustomerid & "'")

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jun 2004
    Posts
    43
    it has error
    txtbalance = DLookup("balance", "qrycheque", "customerid = '" & cmbvcustomerid & "'")

    error:datatype mismatch in criteria expression

    removing "" in balance
    txtbalance = DLookup(balance, "qrycheque", "customerid = '" & cmbvcustomerid & "'")

    error: invalid use of null

    there is a balance for every cusomerid listed so it is not null...

    how can we use select statement in a form event? is it only used for joining tables in query?
    Last edited by oeuf85; 06-13-04 at 22:20.

  14. #14
    Join Date
    Jun 2004
    Posts
    43
    is the word "set" only used in update or it can also be used in select statements

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we are going round in circles. no more from me until you do the following.
    make your combo's _AfterUpdate like this:

    Private Sub yourCombo_AfterUpdate()
    Dim ici As Integer
    For ici = 0 To yourCombo.ColumnCount - 1
    If IsNumeric(yourCombo.Column(ici)) Then
    Debug.Print "Column " & ici & " is numeric"
    Else
    Debug.Print "Column " & ici & " is NOT numeric"
    End If
    Next
    Debug.Print "The bound column is " & yourCombo.BoundColumn
    End Sub

    replace yourCombo with the real name of your combo as displayed in properties/other/name.

    select something in the combo.

    then Ctrl-G (opens the immediate window) and copy/paste the debug output onto this site.

    and by the way,
    txtbalance = DLookup("balance", "qrycheque", "customerid = '" & cmbvcustomerid & "'")
    is 100% accurate if:
    balance and customerid are field names in table (or stored Access query) qrycheque
    AND customerid is a text field
    AND cmbvcustomerid is the name of a combo with bound column = text matching a value in qrycheque.customerid

    all the single and double quotes are required - don't mess with them

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •