have to open form twice to get to correct record

[XV1957]

Hello Access aficionados.
In the following code to add an article to a purchase order line table and then open the form on the purchase order in question , I had to open the window “frmPurchase” twice in succession. If I omit the first docmd.openform statement, the form would open at the first record in the table, not the one defined in the where clause “PUOpen and PUCompID = “ & Me.lstCompany.
I had noticed previously that, when the form “frmPurchase” was already open, Access went to the correct record, and not if the calling program had to open the window, which is why I came up with the idea of replicating this behavior in the code.

With rstC
    .FindLast "PULineNB <> 0 AND PuLineHeadingID = " & rstB!PUID
    If Not .NoMatch Then
        linenb = rstC!PULineNb
    Else
        linenb = 0
    End If
    rstC.AddNew
    rstC!PuLineHeadingID = rstB!PUID
    rstC!PuLineArtno = Me!frmNomenclatuurSubfrm.Form.Artno
    rstC!PULineNb = linenb + 1
    rstC.Update
End With
DoCmd.OpenForm "frmPurchase" 
DoCmd.OpenForm "frmPurchase", acNormal, , "PUOpen AND PUCompID = " & Me.lstCompany

Is this normal behavior, and if not, how can I improve the code?
Thanks in advance for solving the mystery.

 

[vbaInet]

Let me see a screenshot of the Data tab in the Property Sheet of the frmPurchase form.

 

[XV1957]

Hello vbaInet,
here is the screen shot.
The window has Allow additions set to no. After reading yr mail I set it to yes, that did not change the behavior.
See attachment.
Thanks again for your help.

 

[vbaInet]

I should have looked at your criteria in the first instance. So with that said the criteria below is incorrect.

... "PUOpen AND PUCompID = " & Me.lstCompany

PUOpen and PUCompID are separate fields right?

 

[XV1957]

Hi vbaINet,
Indeed these are two different fields.
Having used that statement succesfully before, I now realise it was associated with a .findfirst instruction.
I set out to look for a solution and found the following code which does the trick.
DoCmd.OpenForm "frmPurchase"
Forms("frmPurchase").Recordset.FindFirst "PUCompID = " & Me.lstCompany & " AND PUOpen = True"
Forms("frmPurchase").SetFocus

Is this better in terms of performance? It does take three lines of code instead of two though.
Thanks again for leading me to instructions I was not aware of.

 

[vbaInet]

Always be explicit in your criteria.

The performance should be similar. But are you sure this is what you're after? Your original method and the new one yield different results.

 

[XV1957]

Im am not sure I understand your comment "be explicit in your criteria".
As for the results: I did not notice a difference. What I want is to find the first open Purchase Order for a particular supplier and that is exactly what both methods accomplish. Only, the first seemed odd and counterintuitive but my Access vocabulary is still limited and I am learning every day. Or did I miss something?

 

[vbaInet]

Your original criteria "PUOpen AND PUCompID = " & Me.lstCompany forces Access to try and interpret it as "PUOpen <> False AND PUCompID = " & Me.lstCompany. That's why I said always give Access the full criteria and not half.

The FindFirst method will jump to the record but it will still allow you to navigate to other records in the form, whilst the WHERE argument in the OpenForm method will filter the form the record searched for (and nothing else will be included).

 

[XV1957]

Thank you for your quick reply.
Point 1 must be a bad habit I took over from Progress, but I would only have used it for boolean. I take your point.
Point 2: looks like the second method is the better one then.
The good thing with Access is: it is readily available. The bad thing is: I tried looking for VBA courses in my area, and could not find any. So you jump in and start programming, learning hands on.

 

[vbaInet]

One method isn't better over the other because they produce different results so if you want to allow users to scroll through other records then the second method is the way to go. On the other hand, if you want to restrict users to just the filtered record then method 1 it is.

That's what why you have these sort of forums to help you along the way. There should be something in your area. Perhaps try looking for MS Access courses rather than VBA courses. VBA relates to other products, not just Access.

 

[XV1957]

vbaInet,
be sure I really appreciate this forum and your help.
I hope I can graduate to the level I could be helping others.

 

[vbaInet]

Maybe one day I'll be the one asking you for help

 

[XV1957]

Yes sure, one has to be an optimist!

 

[XV1957]

Hello vbaInet,
looks like this thread is not finished.

In another module,I consistently cause Access to crash whenever I use a variant of the "second method" discussed above as follows:
Forms("frmSale").Recordset.FindFirst "SACompID = " & CompRecid & " AND SAContact = " & PersRecid
Forms("frmsale").SetFocus

However when I use the "First Method" as described above, Access works fine and gives me the right record in the right form.
DoCmd.OpenForm "frmSale"
DoCmd.OpenForm "frmSale", , , "SACompID = " & CompRecid & " AND SAContact = " & PersRecid

Any reason?
Should I continue building an ERP for my little company using such an unstable program? I have had more crashes and forced backups over the last few days.

 

[vbaInet]

Move that code the Load event of the form. You've not given it enough time to load the records.

This means that you'll need to adjust the control/field references accordingly.

 

[XV1957]

Hi vbaInet,
if it were not for this forum and your expertise, I would still be digging.
I understand I have to move the code in question to the on load event of the form itself.
However, how would I get my criteria to the form then? Would I need to pass the form identification and the criteria through a multiple openargs and parse them in the target window on load statement (as I found in various code examples?)
Since I am still missing a good theoretical background on Access, I find passing variables between procedures and/or forms somewhat difficult.

 

[vbaInet]

Have a look at the following link, it should give you some pointers:
http://access.mvps.org/access/forms/frm0031.htm