Correct record and PK displayed. First record used when code on form called.

**BikeToWork** · Oct 29 '15, 03:54 PM

The parameter "[ID]=" & [ID] in your open form procedure may be ambiguous. Call [ID] on the form [txtID] so that your argument is "[ID]=" & [txtID]. See if that makes any difference. I don't know why Access names form controls by field names as default.

**jforbes** · Oct 30 '15, 05:49 PM

If you are still having trouble with this after following BikeToWork's recommendation, then you might want to post the code that runs on open of the Form here as that might have the bug in it.

**zmbd** · Oct 31 '15, 06:38 AM

MS Access Macro and VBA are two entirely different programing languages and what appear to be the same/equivalent commands between the two are often not the same. We would have to have the XML script from the Macro and the VBA to be able to tell you what the differences are...

**Carl Witte** · Nov 3 '15, 03:13 PM

I want to apologize for my late response. I appreciate all of your help. I've decided to use a write around. For future reference here is what I found and did.

The VBA code to open the form works perfectly and always did. The correct record opens up. After the form is open, there is a reference issue where the first record is found through code, not the record currently displayed, even when referencing fields that have various names (txtID instead of ID). I attempted both where and filter methods and had the same response. (correct record displayed, incorrect record upon VBA). I also did a decompile and compact and repair incase this was a random bug that creeps up from time to time.

When I gave up on the VBA direct code and instead called the MS Access Macro for opening only, then had that macro call the other VBA code I needed, the correct record was displayed and the correct record was accessed through VBA.

In conclusion, I couldn't go through it (VBA) so I went around it (Macro).

Once more, thank you for your assistance.

**zmbd** · Nov 3 '15, 06:40 PM

The VBA code to open the form works perfectly and always did. The correct record opens up. After the form is open, there is a reference issue where the first record is found through code, not the record currently displayed, even when referencing fields that have various names (txtID instead of ID).

What is that VBA code?

From what you've given us I cannot duplicate what you are seeing via VBA in my test database.

In conclusion, I couldn't go through it (VBA) so I went around it (Macro).

I'm not sure that's a fair assessment of the situation, I've yet to see something in a form (well outside of the navigationformc ontrol and that's a glitch) that required a macro to accomplish. In fact other than the autokeys and autoexec VBA is usually superior to the Macro.

You've only given us part of the picture and there maybe something going on in your code that is clearing the form's conditional or that is directly accessing the underlying query/table and not the form's current snapshot.

Unless you will submit the aforementioned code, we'll not be able to help and it seems a shame to have to use such a clunky workaround :)

**Carl Witte** · Nov 4 '15, 03:41 PM

First let me say that my comment was in no way a indictment of VBA. Only of my ability to figure out the problem. Per your request I am posting significant portions of my code. I fully agree it is clunky to build a separate macro to do this correctly.

This is on a form I use to order my records for priority testing. Clicking on this opens the specific testing request. This appears to work perfectly. The correct record is displayed on the destination form.

Code:

Private Sub CommandStart_Click()
On Error GoTo CommandStart_Click_Err
DoCmd.SetWarnings False
    'lock the record
    DoCmd.RunSQL "UPDATE dbo_WorkOrders SET dbo_WorkOrders.Status = 1 WHERE (((dbo_WorkOrders.ID)=" & [txtID] & ") AND ((dbo_WorkOrders.Status)=0));"
    Call Result_Connection([txtID])
    'open the record
    DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, "'" & [txtID] & "'"
    DoCmd.Close acForm, "frm_TestList_InOrder", acSaveYes
CommandStart_Click_Exit:
    Exit Sub

CommandStart_Click_Err:
    MsgBox Error$
    Resume CommandStart_Click_Exit
Resume
End Sub

I then manually click this button. And the first record is found with the Form_frm_WorkOr der.ID (and all other Form_frm_WorkOr der references). Not the displayed record. Which is quite confusing to me.

Code:

Public Sub cmdAdd_Test_Click()
On Error GoTo cmdAdd_Test_Click_Err

Dim countER As Integer
Dim TestsNeeded As Integer
Dim responCE As Integer
Dim mySQL As String
Dim mySQL1 As String
Dim STRpart As String
DoCmd.SetWarnings False
TestsNeeded = DCount("*", "dbo_TestSetLine", "[TestSetKey]=" & Form_frm_WorkOrder.TestKey)
countER = DCount("*", "dbo_WO_Results", "[WorkOrderKey]=" & Form_frm_WorkOrder.ID)
If (Form_frm_WorkOrder.Qty * TestsNeeded) < countER Then
    responCE = MsgBox("There are " & Qty & " parts, each requiring " & TestsNeeded & "tests. This should be " & _
        Qty * TestsNeeded & " records." & vbNewLine & "Record count indicates " & countER & " records." & vbNewLine & _
        "Do you want to add another set of tests?", vbYesNo + vbDefaultButton2, "Enough tests listed already")
    If responCE = vbYes Then
        responCE = CInt(InputBox("How many more sets of tests would you like added?" & vbNewLine & "Remember there are " & TestsNeeded & " tests in the test set." & _
                vbNewLine & "Whole numbers greater than 0 and less than 11 only please.", "Additional Sets", 1))
        If responSE < 1 Then
            MsgBox "Unable to add, you have entered a number less than 1", vbInformation, "No Additional Test Records created"
            GoTo cmdAdd_Test_Click_Exit
        ElseIf responCE > 10 Then
            MsgBox "Unable to add, you have entered a number great than 10", vbInformation, "No Additional Test Records created"
            GoTo cmdAdd_Test_Click_Exit
        End If
Else
    If TestsNeeded > 0 Then
        responCE = (Form_frm_WorkOrder.Qty * TestsNeeded - countER) \ TestsNeeded
    Else
        responCE = 0
    End If
End If
'if I get a set from the dbxl page to start with I need to run it the same number of times but at one higher set
If responCE < Form_frm_WorkOrder.Qty Then
    mySQL = "UPDATE dbo_WO_Results SET dbo_WO_Results.ResultComment = 'Part " & Qty & _
            "' WHERE (((dbo_WO_Results.WorkOrderKey)=" & Form_frm_WorkOrder.ID & ") AND ((dbo_WO_Results.ResultComment)='PartA'));"
    DoCmd.RunSQL mySQL
Else

End If
    While responCE > 0
        STRpart = "Part " & responCE
        mySQL = "INSERT INTO dbo_WO_Results ( TestKey, ResultText, WorkOrderKey, ResultComment ) " & _
            "SELECT qryPopTests.TestKey, qryPopTests.Unit, " & Form_frm_WorkOrder.ID & " AS Expr1, '" & STRpart & "' AS Expr2 " & _
            "FROM qryPopTests GROUP BY qryPopTests.TestKey, qryPopTests.Unit " & _
            "HAVING (((Count(qryPopTests.TestKey))>1) AND ((Count(qryPopTests.Unit))>1));"
        mySQL1 = "INSERT INTO dbo_WO_Results ( TestKey, ResultText, WorkOrderKey, ResultComment ) " & _
            "SELECT qryPopTests.TestKey, qryPopTests.Unit, " & Form_frm_WorkOrder.ID & " AS Expr1, '" & STRpart & "' AS Expr2 " & _
            "FROM qryPopTests LEFT JOIN qryDups_in_qryPopTests ON qryPopTests.[TestKey] = qryDups_in_qryPopTests.[TestKey] " & _
            "WHERE (((qryDups_in_qryPopTests.TestKey) Is Null));"

        DoCmd.RunSQL mySQL
        DoCmd.RunSQL mySQL1
        responCE = responCE - 1
    Wend

Form_frm_WorkOrder.dbo_WO_Results_subform.Requery
cmdAdd_Test_Click_Exit:

    Exit Sub

cmdAdd_Test_Click_Err:
    MsgBox Error$
    Resume cmdAdd_Test_Click_Exit
Resume
End Sub

By replacing the line

Code:

    DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID]

with a macro, the second button refers to the correct record, the displayed record.

Thank you for your patience and help.

**zmbd** · Nov 5 '15, 05:43 AM

Sorry, been a very busy day in the lab and it's late here so only a quick glance at our posted code.

What I am not clear about is in the second code block cmdAdd_Test_Cli ck() - Is this the on_click event for a command button on [frm_WorkOrder]?

IF so, then try the construct:

TestsNeeded = DCount("*", _

"dbo_TestSetLin  e", _

"[TestSetKey]=" & _

ME.TestKey)

;
thus, replacing the form_frm_WorkOr der with the Me to ensure that you are referring to the current instance of the form wherein the event is being called from - normally the construct form_[formname] will work; however, I've ran across some unusual glitches where a second copy of the named form was created.
The other workaround if the event is not on the same form:
Forms![formname]![controlname].value
(even though value is normally implied, I like to explicitly declare this :) ) which should refer to the named control on the named form currently loaded - subforms therein would have a similar construct
Forms!Mainform! Subform1.Form!C ontrolName

In either case, I suspect this is what the macro has done and why it works there and not in the VBA code. In ACC2010 and newer, you can cut and paste the ACC-Macro to a text file or within a post (design-view, <ctrl><a><ctrl> <c> to copy to clipboard, <ctrl><v> to paste from clipboard). You can also convert he Macro to VBA using the conversion wizard... often this creates some horrid code; however, you get to see the logic behind the Macro interpreter.

Just for your reference, you might want to download and print the hardcopy of reference constructs from here: MVP:Refer to Form and Subform properties and controls

**jforbes** · Nov 5 '15, 12:47 PM

Something else that concerns me is the OpenArg sent to the Form which usually means there is some code that runs on Load. Which could be causing the unwanted behavior:

Code:

DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, [iCODE]"'" & [txtID] & "'"[/iCODE]

It probably isn't causing your troubles, but it makes me nervous.

**BikeToWork** · Nov 5 '15, 02:46 PM

Code:

    DoCmd.OpenForm "frm_WorkOrder", acNormal, , "[ID]=" & [txtID], , acNormal, "'" & [txtID] & "'"

Is ID actually a text field? If so the where condition should probably be

Code:

"[ID] = '" & Me.txtID & "'"

**zmbd** · Nov 5 '15, 06:01 PM

BikeToWork and JF : Both very good catches, should have seen that myself - too many hours yesterday staring at instrument displays :)