Results 1 to 6 of 6
  1. #1
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56

    form and subform with vba

    Hi I have a form and a subform(order and product details)



    i want when i type the SKU will look up the table and populate description, if there is not exist will add a new product

    I tried this but says record dont found ( a small test)

    -----------------------------------------------

    Private Sub Searchbutt_Click()

    Dim prwanted As String
    Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
    Dim myrecordset As New ADODB.Recordset

    myrecordset.ActiveConnection = cnn1

    myrecordset.Open " SELECT * from products", cnn1, adOpenDynamic, adLockPessimistic
    prwanted = Me!ProductId

    myrecordset.MoveFirst

    myrecordset.Find "productid = """ & prwanted & """"

    If Not myrecordset.EOF Then
    Me!ProductId = myrecordset.Fields("Productid")
    MsgBox "found", , "test"
    Else
    MsgBox "Not Found", , "Test"
    End If

    End Sub
    -----------

    When i use items that exists into product says not found, what im doing wrong?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't use ADO, only DAO. Here is a DAO sub to try:

    Code:
    Private Sub Searchbutt_DAO_Click()
       Dim rst As DAO.Recordset
       Dim sSQL As String
    
       sSQL = "SELECT * FROM products WHERE productid =""" & Me!ProductId & """"
    
       Set rst = CurrentDb.OpenRecordset(sSQL)
    
       If rst.BOF And rst.EOF Then
          MsgBox "Not Found", , "Test"
          'this is where you would add code to add the product to the table.
    
       Else
          '      Me!ProductId = rst.Fields("Productid")
          MsgBox "found product" & rst.Fields("Productid"), , "test"
       End If
    
       'clean up
       rst.Close
       Set rst = Nothing
    
    End Sub
    And here is my attempt at ADO, based on an example from a page from Allen Browne's web site:
    Code:
    Private Sub Searchbutt_ADO_Click()
    
       Dim myrecordset As New ADODB.Recordset
       Dim strSql As String
    
       strSql = "SELECT * from products;"
       myrecordset.Open strSql, CurrentProject.Connection
    
       'this is assuming that there will always be records in the recordset.
       'need
       '  an error handler or
       '  to check recordset record count or
       '  to check for myrecordset.BOF and myrecordset.EOF
       
       myrecordset.MoveFirst
       myrecordset.Find "productid = """ & Me!ProductId & """"
    
       If Not myrecordset.EOF Then
          '      Me!ProductId = myrecordset.Fields("Productid")
          MsgBox "found product" & rst.Fields("Productid"), , "test"
       Else
          MsgBox "Not Found", , "Test"
          'this is where you would add code to add the product to the table.
       End If
       
       'clean up
       rs.Close
       Set rs = Nothing
    
    End Sub
    Last edited by ssanfu; 03-16-2012 at 01:56 PM. Reason: missing line

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, DAO with FindFirst method often seems to work better.
    Last edited by June7; 03-16-2012 at 04:23 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    well not working in ado, i cant use dao , i checked into the reference, but says conflict names, etc...

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have a reference set to "Microsoft DAO 3.6 Object Library"?

    What is(are) the error message(s)?

  6. #6
    thanosgr is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    56
    yes i set it to DAO 3.6, do i have to make it top priority?
    Since the error says that names are conflict

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-29-2012, 09:38 AM
  2. Replies: 35
    Last Post: 12-21-2011, 02:16 PM
  3. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  4. Replies: 5
    Last Post: 11-11-2011, 11:06 AM
  5. Replies: 0
    Last Post: 05-09-2010, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums