Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004

    Unanswered: Question about retrieving a recordset from Access Form

    I am fairly new to SQL and VB programming with Access and I am looking for some code help. I think I have a pretty good idea of what I want to do, I'm just not completely sure how to do it.

    My database only has 3 tables, so it is pretty simple. I have one form where I want a user to be able to enter information into about 7 text boxes and then hit an update button below. I also would like them to be able to enter a value in the primary key text box (what I call the Unit Number) and when they hit ENTER, it should bring up the information for that unit in all of the text boxes. I was trying to do something similar to this:

    Private Sub Unit_Enter()
    strSQL = "select * from [Trailers] where Unit = [Unit Number]"
    Set rsTrailer = CurrentDb.OpenRecordset(strSQL)
    Serial.Value = rsTrailer.Fields("Serial Number")
    ID.Value = rsTrailer.Fields("ID Number")
    End Sub

    I know my syntax is pretty wrong since I'm getting errors. Can anyone help step me in the right direction? Is what I am trying to do something that is possible, and am I thinking about this the right way? Thanks for any help!

  2. #2
    Join Date
    Oct 2003
    Provided Answers: 1
    Try to change strSQL = "select * from [Trailers] where Unit = [Unit Number]" to

    strSQL = "select * from [Trailers] where Unit = " & [Unit Number]

    OR to

    strSQL = "select * from [Trailers] where Unit = '" & [Unit Number] & "'"
    if your Unit Number is a string

  3. #3
    Join Date
    Dec 2004
    that didn't end up working for me. here is how i have my recordset declared:

    Dim rsTrailer As DAO.Recordset

    I know that I have DAO 3.6 referenced, and I tried putting ADODB instead of DAO and it was saying I was missing a reference. I have used just Dim something as Recordset before with no prefix on the Recordset and that has worked. Which should I use, and do I need to reference anything else to get it to work?

  4. #4
    Join Date
    Sep 2003
    Here's some sample code demonstrating assigning values to different types of controls ... (production code):

    Function RetrieveThePO() As Integer
        On Error GoTo Err_RTPO
        Dim TrgRecSet As ADODB.Recordset
        Set TrgRecSet = New ADODB.Recordset
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        SQLString = "SELECT * FROM [Purchase Orders] WHERE ([Tracking #]=" & TrackingNumber & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            ' PO #
            PO_Txt.Value = MyRecSet.Fields(1).Value & ""
            ' PA/PR (Requisition #)
            PAPR_Txt.Value = MyRecSet.Fields(2).Value & ""
            ' Blanket Type
            BTypeComboBox.Value = MyRecSet.Fields(3).Value
            ' Blanket #
            BlanketTxt.Value = MyRecSet.Fields(4).Value & ""
            ' Expiration Date
            If Not IsNull(MyRecSet.Fields(5).Value) Then
                ExpirationTxt.Value = Format(MyRecSet.Fields(5).Value, "Short Date")
                If MyRecSet.Fields(5).Value < Date Then ExpiredBlanketLbl.Visible = True
            End If
            ' Status
            PO_Status = MyRecSet.Fields(6).Value
            StatusGroup.Value = MyRecSet.Fields(6).Value
            ' Order Date
            OrderDateLbl.Caption = Format(MyRecSet.Fields(7).Value, "Short Date")
            ' Date Required
            DtRequiredTxt.Value = IIf(Not IsNull(MyRecSet.Fields(8).Value), MyRecSet.Fields(8).Value, Date)
            ' Requestor ID
            RequestedByComboBox.Value = MyRecSet.Fields(9).Value & ""
            ' Deliver To
            DeliverToTxt.Value = MyRecSet.Fields(10).Value & ""
            ' Supplier ID
            SupplierID = MyRecSet.Fields(11).Value
            SugSupplierComboBox.Value = MyRecSet.Fields(11).Value
            SugSupplierComboBox.Locked = True
            ' Buyer Code
            BuyerCodeTxt.Value = MyRecSet.Fields(12).Value & ""
            ' Total Cost Book
    '        TotalCostBookTxt.Value = MyRecSet.Fields(13).Value
            ' Comments
            NotesTxt.Value = MyRecSet.Fields(15).Value & ""
        End If
        Set TrgRecSet = Nothing
        OriginalItemCount = ItemCount
        If HaveAnItem Then
            RemoveButton.Enabled = True
            AllocateButton.Enabled = True
            CommentButton.Enabled = True
            SplitButton.Enabled = True
            EditItemButton.Enabled = True
        End If
        RetrieveThePO = 1
        Exit Function
        MsgBox Err.Number & ": " & Err.Description
        RetrieveThePO = 0
        Resume Exit_RTPO
    End Function
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Dec 2004
    Thanks...I think I have part of it working. Now I am trying to get the enter key to register as a key press event in my text box. From searching online it seems like it is a command key and wont trigger the event unless you change its keypreview setting to yes. How do I get into the keypreview properties in access?

Posting Permissions

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