Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2007
    Posts
    34

    Unanswered: txtbox to show values from query

    Hi there,

    Currently I have a tabular form where the data source is coming from a Table1

    So my form is set up like so

    | RQ# | Date | Country | Rank |
    [txtbox1] [txtbox2] [txtbox3] |[txtbox4]

    txtbox1 and txtbox2 is retrieving their data directly from Table1.

    txtbox3 and txtbox4's data needs to come from a query I created - let's say Query1.

    How do I make it so that that txtbox3&4 show the data from the query.

    Also, if I set up the form in this manner will I be able to enter in new data still?

    Thanks,

    - wayne

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Assuming query1 isn't getting its data from table1, you would have to use a DLookup to display data from it, and that would not be update-able. The data from table1 would still be. You could get the data via recordset and place it there, but you'd essentially have an unbound form.

    Depending on how the two relate to each other, a subform might be an option.
    Paul

  3. #3
    Join Date
    Sep 2007
    Posts
    34
    Query1 would be retrieving from Table2, where Table1 and Table2 are related by RQ#.

    I don't mind if the Query aspect is not update-able as long as I can update fields from Table1.

    And I would like to stay away from a subform if possible.

    Thanks,

    - wayne

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Take a look at DLookup then:

    General: DLookup Usage Samples
    Paul

  5. #5
    Join Date
    Sep 2007
    Posts
    34
    Hi again,

    It's been some time since I've looked into this but the project came back and looks like I'm back to working on this database.

    I managed to get the dlookup portion but not getting the results I'd like.

    So basically I have a main form and a sub form. The subform is a tabular form (can be viewed in the attachment section)

    The first two fields are controlled by a table, and the other text boxes are unbound.

    For the textbox that says "customrezero" it should be populating the customer name for each respective request.

    Ie, Request54 should be customerzero, Request102 should be customeralpha, etc.

    The code I used on the unbound text box is the following:

    +++++++++++++++++++
    Private Sub Form_Current()

    Dim Customer As String
    Dim RQCriteria As String

    RQCriteria = "[RQ]=" & "'" & Me![RQ] & "'"

    If IsNull(DLookup("[Customer]", "Table_Main", RQCriteria)) Then

    Me.txtCustomer.Value = "N/A"

    Else

    Customer = DLookup("[Customer]", "Table_Main", RQCriteria)
    Me.txtCustomer.Value = Customer

    End If

    End Sub
    +++++++++++++++++++

    But instead what I'm getting is customerzero (or whatever the first entry is) for all the textboxes in the tabular box.

    Is there a way I can show each respective view?

    Thanks in advance!
    Attached Thumbnails Attached Thumbnails tabularform.JPG  

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you click on the second record, do they all change to the correct name for that record? An unbound textbox will show the same value for every record. Presuming the formula is working correctly, you probably want the textbox bound to:

    =Nz(DLookup(...), "Whatever")
    Paul

  7. #7
    Join Date
    Sep 2007
    Posts
    34
    Thanks again for the quick reply Paul.

    Tried the Nz(dlookup) but I'm still retrieving the same customer name for the unbound text box.

    Is using the private subform_current() not the thing to use maybe?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No event will work for what you want to do. An unbound textbox being set from code will display the same value for every record. I would try that as the control source of the textbox.
    Paul

  9. #9
    Join Date
    Sep 2007
    Posts
    34
    Thanks Paul, that did trick!

    Many thanks!

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help; just noticed it had been a year and a half!
    Paul

  11. #11
    Join Date
    Sep 2007
    Posts
    34
    Yes it has been - the project was postponed and when it was re-introduced i was given the task of updating the db again...

    cheers!

Posting Permissions

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