Simple Question Searches do not give me an answer. DoCmd.GoToRecord acForm

[MarkVenes]

I have a large table that has 8 forms to input the data into the table. The problem I just realized that I have is that I am using these lines of code to close the form that the applicant is working on and go to the next form and go to the record that they were working on. The problem is what if I have two people entering data into the same table at the same time. So i think I need to specify what record to go to and thereby make sure that the data is being entered into the proper record. Any Ideas let me know.



FYI it is a table for new applicants for the office. I think I need to declare a "where" statement and "look-up" or something, for the current record number before I close the form and then tell it to go to that record when it opens the next form. Am I somewhere in the ball park here? Or is there an acSame type expression.

Private Sub NextPage2_Click()
On Error GoTo NextPage2_Click_Err

With CodeContextObject
If (.Ready2 = False) Then
Beep
MsgBox "You must check the box to the left certifying the information on this page as accurate before you can go to the next page.", vbExclamation, "Must Verify Answers"
End
End If
DoCmd.Close acForm, "Application2"
DoCmd.OpenForm "Application3", acNormal, "", "[Ready2]=True", acEdit, acNormal
DoCmd.GoToRecord acForm, "Application3", acLast
' What if there are two people entering an application at the same time, who's information goes where?
Ready2 = False
End With

NextPage2_Click_Exit:
Exit Sub

NextPage2_Click_Err:
MsgBox Error$
Resume NextPage2_Click_Exit

[pbaldy]

Does this work for you?

BaldyWeb wherecondition

You're using the argument now, with "[Ready2]=True". It sounds like using the ID would be more exact.

[MarkVenes]

I will give it a try and see what happens. Thank You excellent page. Also, to be honest with you I think my brain is fried, from working too many hours on this project. I have no Idea how I wrote that line of code, but it is working. The ready2 field is a boolean check box and for some reason it works.

[pbaldy]

Happy to help. What you have would work, but I thought wasn't filtering to the exact record. Post back if you're still stuck.

[MarkVenes]

Ok I am having some trouble.

This is the line of code you suggested:

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

This is what I have so far:

DoCmd.OpenForm "SecondFormName", , , "ApplicantID = " & Me.ControlName

I understand that the "&" is used for a string value and the "Me." refers to the object or "form" that I am currently on, But after that I am lost. I am trying to understand ControlName and I would think that it would refer to a filed ??? So do I put "Me.ApplicantID" in that place?

[MarkVenes]

Tried this got an "Expected end of statement" error.

DoCmd.GoToRecord acForm, "Application3", "ApplicantID =" Me.ApplicantID ( with the Me. highlighted)

[MarkVenes]

Tried this it did not work. Not sure about the "," numbers.

DoCmd.GoToRecord acForm, "Application3", , ApplicantID = Me.ApplicantID

[MarkVenes]

Ok this does not work from Form "Application1" to form "Application2"

DoCmd.GoToRecord acForm, "Application2", ApplicantID = Me.ApplicantID

But this DOES work for going from Form "Application2" to Form "Application3"

DoCmd.GoToRecord acForm, "Application3", ApplicantID = Me.ApplicantID

Any idea as to why?

O I also tried it this way

DoCmd.GoToRecord acForm, "Application3", [ApplicantID] = Me.ApplicantID


Here is the code so far

Private Sub NextPage2_Click()
On Error GoTo NextPage2_Click_Err

Me.Refresh
With CodeContextObject
If (.Ready2 = False) Then
Beep
MsgBox "You must check the box to the left certifying the information on this page as accurate before you can go to the next page.", vbExclamation, "Must Verify Answers"
End
End If
DoCmd.OpenForm "Application3", acNormal, "", "[Ready2]=True", acEdit, acNormal
DoCmd.GoToRecord acForm, "Application3", ApplicantID = Me.ApplicantID
Me.Ready2 = False
DoCmd.Close acForm, "Application2"
End With

NextPage2_Click_Exit:
Exit Sub

NextPage2_Click_Err:
MsgBox Error$
Resume NextPage2_Click_Exit

End Sub

[pbaldy]

Well, to start at the first, yes, you'd use your name. This:

DoCmd.OpenForm "SecondFormName", , , "ApplicantID = " & Me.ControlName

Would be like

DoCmd.OpenForm "Application2", , , "ApplicantID = " & Me.ApplicantID

[MarkVenes]

First, again thank you for your help it is much appreciated.

Remembering that we were working with the GotoRecord portion: Maybe I am missing something.
Are you telling me that in order to go to a specific record I need to;

1. Alter my DoCmd.OpenForm line?

...... If so do I do this for each form or just the first form? Since the present GotoRecord worked when going from form 2 to form 3

2. Remove the DoCmd.GotoRecord acForm line of code?

....... If so do I remove this line from every form as well?

And this will solve my problem? If so, how do I alter the line so that it still retains the [Ready]=True ?

This is my line presently: DoCmd.OpenForm "Application2", acNormal, "", "[Ready]=True", acEdit, acNormal

Now, if I follow the logic in your reply, then is should look something line this? Based upon the current line.

DoCmd.OpenForm "Application2", acNormal , "", "[Ready]=True AND "ApplicantID = " & Me.ApplicantID , acEdit, acNormal

Not sure about combining expressions with the AND and how to do that argument.

[pbaldy]

Well, my feeling is that it's better to get to the desired record in one step rather than two. You're really close with that last code. Take out the quotes right after the AND.

[MarkVenes]

OK based on all the information you have given me this is what the Sub Looks like. BUT it does not work. When I go from Form Application1 to Form Application2 it starts a new record in the table. So, I am assuming I screwed up somewhere. Any ideas?


Private Sub NEXTPAGE_Click()
On Error GoTo NEXTPAGE_Click_Err

With CodeContextObject
If (.Ready = False) Then
Beep
MsgBox "You must check the box to the left certifying the information on this page as accurate before you can go to the next page.", vbExclamation, "Must Verify Answers"
End
End If
DoCmd.OpenForm "Application2", acNormal, "", "[Ready]=True AND ApplicantID = " & Me.ApplicantID, acEdit, acNormal
Me.Ready = False
DoCmd.Close acForm, "Application1"
End With

NEXTPAGE_Click_Exit:
Exit Sub

NEXTPAGE_Click_Err:
MsgBox Error$
Resume NEXTPAGE_Click_Exit

End Sub


Let me Guess the AND should look like this instead And

[MarkVenes]

Nope that was not it.

[pbaldy]

Can you post the db here, or a representative sample?

[MarkVenes]

Ignore this message.