Unable to open form to specified record

[darkwind]

Hi,



Access 2013. So I've got a form (frmEdit) that's intended to allow users to open up another form (frmProjectLogin) in order to edit the information of a specified job there, by inputting the job # (into txtLoginForm). This form was working perfectly until Friday, when I made some (seemingly unrelated) changes to the database. Now, clicking btnLogInForm will open the form, but create a new job instead of opening the existing one. Here's the related code:

Private Sub btnLogInForm_Click()

    Dim projectID
    projectID = DLookup("projectID", "tblProjectWorking", "batchPrefix = '" & Left(Me.txtLogInForm, 2) & "' and [Batch No] = " & Right(Me.txtLogInForm, 4) & "")

    DoCmd.OpenForm "frmProjectLogin", , , "projectID = " & projectID

    Form_frmProjectLogin.txtInputValid = 1
    DoCmd.Close acForm, "frmEdit"



    Form_frmProjectLogin.SetFocus




End Sub

• Converts user-input txtLoginForm, a batch number writen as 17-1000, into the projectID for the job. This is known to work correctly (I have tested by removing the rest of the code and having this output into a text box on frmEdit. The projectID is always correct).
• Should open frmProjectLogin with the specified job, but instead opens frmProjectLogin with a new project.
• Unnrelated, but sets an unbound field to 1 for validation purposes when exiting frmProjectLogin
• Closes frmEdit
• Sets focus to frmProjectLogin

Changes I made last week:

• Changed field types for: frmProjectLogin.[projectname] and frmProjectLogin.[projectaddress] from text box to combo box
• Added a field, [tatID], to frmProjectLogin and tblProjectWorking (control source).
• Added a related table, tblTAT.
• Added the following code to update the status of the project, conditionally:

If IsNull(Me.status) = True Then
        status = "Log-in Complete / Ready to Prep"
        Else
        End If

• Added the following code to frmProjectLogin to set the tatID to the correct value based on the selected TAT:

Private Sub TAT_AfterUpdate()
'updates the tatID based on the selected TAT
On Error GoTo tat_Error:
    Me.tatID = DLookup("[tatID]", "[tblTAT]", "[Turn Around Time] =" & "'" & Me.TAT & "'")
On Error GoTo 0
[pre existing code]
Exit Sub
tat_Error:
    MsgBox "tatID Error. Verify the selected Turn Around Time is correct, then notify Admin if not resolved.", vbOKOnly
    Cancel = True
End Sub

• Edited code in frmProjectLogin to handle "not provided" project names when sending email notifications. Will post if requested; I'll have to edit it to hide some info
• Edited code in frmProjectLogin to handle "not provided" project names when sending text notifications. Will post if requested; I'll have to edit it to hide some info.
• Some changes to a completely unrelated report

I looked back through all my changes and couldn't find anything suspicious to me on Friday. So my next step was to go back to the previous version of the database, implement the changes one by one, and test to see when the error started occurring. It never did, so at the time I assumed I made some wild edit and started using that new version. Then this morning, suddenly I find the error is back (no one was in the office over the weekend, I was last out first in, so no way anyone else made any changes). Any ideas on how to resolve this? Failing that, any ideas on which changes to look at as potential causes?

Thanks in advance for any help!

[CJ_London]

perhaps you have set the form dataentry property to true?

[Missinglinq]

When you try opening a Form to a given Record, like this, if matching data doesn't appear in the secondary Form, Access goes to a new Record, which is what you're experiencing here, I believe. My guess would be that the problem is that you've named your Variable projectID, which is the same name as the Field you're trying to match, and this has the Access Gnomes totally confused!

Change the name of your Variable to something else, like varProjectID, and see what happens.

You should probably Dim the Variable to an explicit data type, like String or Integer (whichever projectID is) as simply using

Dim projectID

dims it, by default, as a Variant, and can sometimes lead to problems.

Linq ;0)>

[darkwind]

Well, it seems the issue has been mysteriously resolved... which I don't like at all, because that means it might come back . Someone must be messing with this, but no one is owning up to it.

perhaps you have set the form dataentry property to true?

Good thought. I checked just now and it's set to false, but since the form is working again, that doesn't mean much. The first thing to check if the issue comes back!

When you try opening a Form to a given Record, like this, if matching data doesn't appear in the secondary Form, Access goes to a new Record, which is what you're experiencing here, I believe. My guess would be that the problem is that you've named your Variable projectID, which is the same name as the Field you're trying to match, and this has the Access Gnomes totally confused!

Change the name of your Variable to something else, like varProjectID, and see what happens.

You should probably Dim the Variable to an explicit data type, like String or Integer (whichever projectID is) as simply using

Dim projectID

dims it, by default, as a Variant, and can sometimes lead to problems.

Linq ;0)>

Yeah, i was just being lazy/ in a hurry when I wrote that. I almost changed it before posting, but as I said, I made no changes to that code before this problem occurred, so I was pretty confident it wasn't the issue (and I didn't want to disturb the evidence in the event that it was). I should probably go back through and remove evidence of my laziness in a few other places...

Thanks to you both!