Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Search Record

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: Search Record

    As i have a save button, in the save button, i have a function to add new record. But the next time when they click the save button, i would like to be able to edit that record not add another new record. First i want to search whehter the record is already in the database. I have the receiptNumber, i want to check whether the receiptNumber is in the databae. How to write vba code? After i find the record, i am going to use .edit. to update this record. Thank you very much!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using a DAO recordset
    Code:
    Dim db As Database
    Dim rs As DAO.Recordset
    
    'Access the database
    Set dB = CurrentDb()
    
    'Open Receipts table
    Set rs= dB.OpenRecordset("Receipts", dbOpenDynaset)
    rs.FindFirst "receiptNumber = " & Me.txtReceiptNumber.Value
    
    If rs.EOF Or rs.BOF Then
     'no records found
    Else
     'record found
    End If
    This code is untested, so let me know how you get on!
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's an example using ADO code to see if the receipt number exists. If so, it goes to that record. ReceiptNum on the form must be enabled and visible for this to work:
    Code:
    Function FindGotoRecord(ReceiptNum As Variant)
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from MyTable where ReceiptNum = " & ReceiptNum & ""
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Me.ReceiptNum.SetFocus
        DoCmd.FindRecord ReceiptNum
    End If
    rs.Close
    Set rs = nothin
    End Function
    Last edited by gvee; 08-13-07 at 11:49. Reason: [CODE] tags added
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Georgev, - we must have posted at the exact same time.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    5 minute gap

    Refresh my memory... Did we decide that ADO or DAO was the better of the two?
    EDIT: I think it was ADO...
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    Thank you so much!
    I am working on edit the record once it was found in the datababae. But it didn't work. Could you please take a look my code:

    I think the line of code: Set rstTableE = dbs.OpenRecordset(strSelect)
    caused the stop to edit.



    Dim dbsE As DAO.Database
    Set dbsE = CurrentDb
    Dim rstTableE As DAO.Recordset

    Dim strSelect As String
    strSelect = "Select * from tblReceipt where ReceiptNumber = " & txtReceiptNumber

    Set rstTableE = dbs.OpenRecordset(strSelect)


    With rstTableE
    .Edit

    !ReceiptDate = Me.txtReceiptDate
    !Fund = Me.cboFund.Value
    !Category = Me.lstCategory.Value

    !ReceivedFromFName = Me.txtReceivedFromFName
    !ReceivedFromLName = Me.txtReceivedFromLName
    !Company = Me.txtCompany
    !Country = Me.txtCountry
    !Memo = Me.tMemo


    .Update
    rstTableE.Close
    Last edited by yyu; 08-13-07 at 12:00.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by georgev
    5 minute gap

    Refresh my memory... Did we decide that ADO or DAO was the better of the two?
    EDIT: I think it was ADO...
    I've been using ADO for so long now that I've lost some of my touch with DAO and now can't even remember some of the methods in DAO. I don't think 1 verses the other is better, I think it's a preference thing.

    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2004
    Posts
    660
    Can i use DAO and ADO in same project? Thanks. I found using DAO the code you posted didn't work for me. It is always go to else. So they alway find the record. Maybe something wrong with my code?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Run this for me:
    Code:
    MsgBox txtReceiptNumber
    MsgBox Me.txtReceiptNumber.Value
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2004
    Posts
    660
    I did try this too. but it still go to else. I tried the second methond, it works. Thanks. Now i also want to be able to edit using VBA. It didn't work. Can you all help?
    Last edited by yyu; 08-13-07 at 15:33.

  11. #11
    Join Date
    Mar 2004
    Posts
    660
    I couldn't edit a record. I really need it work now. Could you please post your code for me? . May i use wrong method. Thanks.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this your code for editing?
    Code:
    With rstTableE
    .Edit
    
    !ReceiptDate = Me.txtReceiptDate
    !Fund = Me.cboFund.Value
    !Category = Me.lstCategory.Value
    
    !ReceivedFromFName = Me.txtReceivedFromFName
    !ReceivedFromLName = Me.txtReceivedFromLName
    !Company = Me.txtCompany
    !Country = Me.txtCountry
    !Memo = Me.tMemo
    
    .Update
    rstTableE.Close
    Just noted that you're missing an End With...

    Step through the code and tell us what's not working?
    It may be more obvious if you try
    Code:
    !ReceivedFromFName = "Test"
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2004
    Posts
    660
    I did have end with.

    I use debug, When i run the code:

    Dim strSelect As String
    strSelect = "Select * from tblReceipt where ReceiptNumber = " & txtReceiptNumber

    Set rstTableE = dbs.OpenRecordset(strSelect)


    It showed up an error message: Object variable or with block variable not set.
    Is these lines of code have problem? Do i have to have any other parameter in dbs.OpenRecordset??
    Thanks.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take another look back at the DAO code I posted above. Particularly the OpenRecordset line and see if you can spot any differences.
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2004
    Posts
    660
    I tried !ReceivedFromFname="Test", it still showed "Object Required". It didn't go to this line. It stopped at :

    Set rstTableE = dbs.OpenRecordset(strSelect)

    Thanks.
    Last edited by yyu; 08-14-07 at 10:09.

Posting Permissions

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