Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    melhado
    Guest

    Moving pointer to new record

    Using Access 2000SR-1, NT 4 SP6

    I have a form/subform in which I need to put a button to execute code that will allow the user to put in 2 pieces of data (which also form the compound primary key for this table, and provides the link to the child form) to identify a record in the main table, move the pointer to that record, and if there is no matching record, open a new record.

    Data entry and collection is not typical. The parent form needs 2 fields before entering data in a child record, and not all information is filled out at one time, so they need the flexibility to go back and forth. Thus, I cannot use a filter, due to it presenting the filtered recordset

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    QBF would be overkill for this. It's primarily used to filter recordsets in a form or to create the SQL for a filtered recordset. When you get an error messagebox, hit ctl-Break and that should take you into the code, either immediately or after you click a button on the messagebox. I don't see anything obvious in the code, unless one or both of your parameters are actually numbers rather than strings.

    Recordsetclone navigation would be simpler than what you're doing, since it uses the existing recordset and you can control what the bookmark does. All you need to create is the FindNext criteria, which is your where statements without the "where". Test for NoMatch, which tells you the record wasn't found. If Not NoMatch, then just set the current recordset's bookmark equal to the recordsetclone's bookmark. Try something like this, which assumes the records you're searching for is in the current form:

    <pre>Private Sub cmdFndBlt_Click()
    On Error GoTo Err_cmdFndBlt_Click
    Dim rstB As Recordset
    Dim strSearch As String, strSN As String, strPN As String

    Set rstB = Me.RecordsetClone
    strSN = Trim(InputBox("Enter Billet ID - # only", "Serial Number"))
    strPN = Trim(InputBox("Enter Part Number", "Part Number"))
    strSearch = "[SerNum] = '" & strSN & "' AND "
    strSearch = strSearch & "[PTnum] = '" & strPN & "'"
    rstB.FindFirst strSearch
    If Not rstB.NoMatch Then
    Me.Bookmark = rstB.Bookmark
    Else
    DoCmd.GoToRecord , , acNewRec
    End If
    Set rstB = Nothing
    Exit_cmdFndBlt_Click:
    Exit SubErr_cmdFndBlt_Click:
    MsgBox Err.Description
    Resume Exit_cmdFndBlt_Click
    End Sub</pre>

    Charlotte

  3. #3
    melhado
    Guest

    Re: Moving pointer to new record

    Charlotte,

    Thank you very much for the idea. Your assumption that the record being searched for exists in the same recordset is correct -- if it doesn't, I want to create a new record in that recordset for the user to enter the new values. Maybe in version 2.0 I will have it automatically fill in the values from the Input boxes.

    However, I am now getting an error I can make no sense of -- it is stopping at the rstB.FindFirst line, telling me that "Method or Data Member not found." Checking it, I found that the drop down only let me put "Find" as the property, and doing that made the same error come up for the "If Not rstB.NoMatch" line, stating that NoMatch wasn't valid.

    I looked in the Help for FindFirst and the example uses a function called FindAny, but it looks like that is for a different use than just returning the record meeting the search criteria.

    Anyone can explain what is going on?

    Appreciate the help

    'dave

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    Ah, you didn't specify ADO and your posted code was DAO, so that's what I used. If you declare the recordset as DAO.Recordset it should work, assuming you have a DAO reference set.

    Normally, if you have both references set (and I generally do it anyhow), you declare the object variables as specific types, i.e., DAO.Database or ADODB.Recordset or DAO.Recordset, to keep from confusing the compiler. Since you didn't specify the object model, I assumed you had only the DAO reference set.

    If you don't have the DAO reference set, your code won't work in either version. If you DO have it set, along with the ADO reference, you need to go through the code and specify object models. The sequence of the references tells Access which object model to assume, but it isn't really safe to rely on that when you mix the two.
    Charlotte

  5. #5
    melhado
    Guest

    Re: Moving pointer to new record

    Charlotte,

    Thank you for a most valuable tip. I had not realized that ADO was the default object model -- is that new in Access 2K ... never had to do that in '97? Oh, and yes, I did have DAO referenced. Thank you for the pointer.

    All that is left is to pass the value of the input boxes to the new record when it goes there.

    Again, thanks for your assistance

    'dave

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    Yes, it's new in 2K. Access 97 will let you use ADO if you have MDAC installed, but its default is DAO. Access 2000 will let you use DAO if you set the reference, but its default is ADO.
    Charlotte

  7. #7
    melhado
    Guest

    Re: Moving pointer to new record

    That explains. BTW, your advice helped me resolve another issue, so more thanks goes to you.

    Are there advantages to using the ADODB model?

    'dave

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    DAO will reach (or has reached) a point where it will not be extended any further. Microsoft has switched to ADO as the data access model of choice and there are good reasons for it.

    ADO is highly extensible. The basic model allows for an almost unlimited number of provider-specific properties, and it happily handles things like XML and not relational datasources like email and plain text. It's far more powerful and farther-reaching than DAO, but that means it is sometimes harder to use with Access, since DAO was optimized for the Jet engine.

    The best reason I've found to use it is that you can do things you simply cannot do with DAO, no matter what. It offers you shaped recordsets, disconnected recordsets, and even recordsets that exist only in memory with no table at all behind them. It allows you to "persist" a recordset, which means you can save it as a file. Then you can take that file, give it to someone else and allow them to open the recordset using ADO code, edit the records, save them back to the file, and then hand you the file to run a simple .UpdateBatch to sync it back to the table. Oh, did I mention that you don't have to be using Access to do the editing? Any application that supports ADO and can open an ADO recordset will do nicely. Neat, huh?
    Charlotte

  9. #9
    melhado
    Guest

    Re: Moving pointer to new record

    WOW -- sounds very promising. However, to do the findfirst I wanted would have required doing the find method with its last argument being findfirst=true?? -- or so I gathered from the help file, but could find nothing to test for NoMatch.. and then you helped me with the DAO spec and my research ended <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Are there any particular resources you have found to learn more about the details of ADO?

    thanks again

    'dave

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    In ADO, there is no FindFirst method, it's just Find. And there is no NoMatch. Instead you test for EOF. If the EOF is true, there was no match. The additional arguments in Find allow you to tell it where to start and which direction to move and how many records to skip from the current bookmark before starting the search. If you're looping, you need to set the offset to 1 or it keeps finding the record it's on.

    The Access 2000 Developer's Handbook uses mainly ADO, so it's a good reference for ADO in Access. The rest of the books are primarily ADO in C++, etc., but a few are pointed at VB developers. I like Mike Gunderloy's book, The Visual Basic Developer's Guide to ADO. His has the best handling of things like Shaped recordsets, but don't expect it to be easy.
    Charlotte

  11. #11
    melhado
    Guest

    Re: Moving pointer to new record

    Right after I hit the post-it button, I thought of EOF, but thank you for confirming it.

    Thanks for the resource references, I will have to look further into them.. am new to A2K, and some of the differences between it and 97 are not so subtle.

    'dave

  12. #12
    melhado
    Guest

    Re: Moving pointer to new record

    Something strange happened -- this code and all worked perfectly, even after I got it to pass the values from the input boxes to the new record. It even set up a new set of subform records, as it should. Suddenly, today, after doing a routine compact/repair, it will do the new record with the value passed, but it inherits the subform records from whatever record the search was initiated on!!

    I checked to see if it was doing anything in the tables, and I could not see that it was doing anything different than when it was working correctly. Also checked the link between the form/sub and that was fine. I manually navigated the main form back a few records and then moved it back to where the record was, and it had a fresh subform record. If I searched for a record that existed, it works without flaw, only when a new record is being made. The idea here is that if the search results in a new record, the user can go directly to the subform portion (which is transparent to them) and start entering data.. that is how it worked yesterday. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    I have posted the new revised code below. Is there code needed to refresh the link -- which I didn't need yesterday?

    <pre>Private Sub cmdFndBlt_Click()
    On Error GoTo Err_cmdFndBlt_Click

    Dim rst As DAO.Recordset
    Dim strSearch As String, strSN As String, strPN As String

    Set rst = Me.RecordsetClone

    <font color=448800>'Get user input for search and store in variable</font color=448800>
    strSN = Trim(InputBox("Enter Billet ID - # only", "Serial Number"))
    strPN = Trim(InputBox("Enter Part Number", "Part Number"))

    strSearch = "[SerNum] = '" & strSN & "' AND "
    strSearch = strSearch & "[PTnum] = '" & strPN & "'"

    <font color=448800>'Find record, if a match, show it with related
    'subform records, else go to new record</font color=448800>
    rst.FindFirst strSearch
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    Else
    DoCmd.GoToRecord , , acNewRec

    <font color=448800>'populate new main form record with values
    'passed from Input Boxes</font color=448800>
    With rst
    .AddNew
    !SerNum = strSN
    !PTnum = strPN
    .Update
    End With
    End If

    Set rst = Nothing

    Exit_cmdFndBlt_Click:
    Exit Sub

    Err_cmdFndBlt_Click:
    MsgBox Err.Description
    Resume Exit_cmdFndBlt_Click

    End Sub
    </pre>


    Appreciate any ideas that will fix whatever broke

    Thank you
    'dave

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving pointer to new record

    I'm not sure what you mean about setting up a new set of subform records. Your code is creating a new parent record. If your parent form and child form are linked on the Master/Child links, then the subform should only display records related to the parent record. If the parent record is new, the subform shouldn't contain anything. Do you have these forms set up for data entry?
    Charlotte

  14. #14
    melhado
    Guest

    Re: Moving pointer to new record

    Charlotte,

    You understood perfectly, and that is exactly what it did when I first got it working (with your able assistance, I might add -- thank you again). The master and child forms are linked and work during regular navigation; and when I use the find button with this code, and it finds a record, it links them together as it is supposed to. It is when I put in a set of criteria that results in no match, that it creates the new record with the criteria filled in where I want it, as it should; however, it shows up with the child records of the parent record that the search was done from!! Oddly, if I navigate back one parent record and then return to it, the child form is blank as it should be! Unfortunately, this is not something I am comfortable with the users remembering to do or notice.

    It didn't act this way at first, it started do this only after I placed another button on the form that only runs a query, and then compacted the database. That is when it started acting this way.

    I looked in the VBA editor and saw that the other command button code appeared after this code, but was separated by the line and a new "Private Sub ...End Sub" section, so I don't think that has anything to do with this fluke happening. I checked the links of the parent/child forms and it was OK. I added a new record manually, and that worked fine as well. It is only happening when a new record is created by the code.

    As to having data entry set to true, it is not, as existing records need to be shown and accessible, edited, added to, deleted, changed and anything else one could think of -- data collection and test events run far from parallel courses. In addition, it worked perfectly previous to the above mentioned events. I did notice, however, that I have both these lines at top -- but they have been there all along ..

    <pre><font color=blue>Option Compare Database
    Option Explicit</font color=blue></pre>


    Might that be the culprit? Why wouldn't it have been a problem before? I don't think I am using it, but it's probably left over from previous coding attempts.

    Also, Charlotte, I came across your advice on an unrelated topic about compressing a database for posting -- you mention a /decompile switch. I am not familiar with that at all, but wondering if it may be the solution to this, or does it do things I don't want done?

    This one is baffling me .. any suggestions would help.

    Thank you,

    'dave

  15. #15
    melhado
    Guest

    Re: Moving pointer to new record

    In regards to ADO v DAO, I found quite a bit of documentation at Microsoft's site:

    <A target="_blank" HREF=http://msdn.microsoft.com/library/techart/daotoadoupdate.htm>http://msdn.microsoft.com/library/te...oadoupdate.htm</A>

Page 1 of 2 12 LastLast

Posting Permissions

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