Opening Form to a Specific Record

[PPat]

Hey Guys,

To simplify, I'm currently writing a module that (at one point) will allow me to open the "90-Hr Waiting PU Form" to the same record that was on the "90-Hr Sheet Request Form". Here is the full code for the module below:

 DoCmd.OpenQuery "Append from Request to PU (Step 1 to 2)", acViewNormal, acEdit
 DoCmd.OpenForm "90-Hr Waiting PU Form", acNormal, "", "[90-Hr Sheet Request Form].StudentIDTextBox" = "[90-Hr Waiting PU Form].StudentIDTextBox", acEdit, acNormal
 DoCmd.OpenQuery "Delete Query (from Request)", acViewNormal, acEdit
 DoCmd.Close acForm, "90-Hr Sheet Request Form"

The command where I open the form (the one in bold) is the one I'm focusing on. I need to specify the "Where Condition": to open the PU Form to the record that was just appended in the previous line. I'm thinking of making it a filter, so that it opens to where the StudentIDTextBox in form 2 is equal to StudentIDTextBox in form 1. How would you write or change this code to get it to work?

Thank you for your time.

[June7]

Concatenate variables, reference to control on form is a variable.

DoCmd.OpenForm "90-Hr Waiting PU Form", acNormal, , "StudentID = '" & Me.StudentIDTextBox & "'"

What is the name of student ID field? Is it a number or text field? If it is number then remove the apostrophe delimiters.

[PPat]

The name of the field is simply "Student ID" (a number field), and on the form it is brought up in the "StudentIDTextBox". I have to check it tomorrow when I'm back at work, but what would it be without the delimiters? Thank you again.

[June7]

Yes, without delimiters. If the field name has space must enclose in []:

"[Student ID]=" & Me.StudentIDTextBox

Advise not to use spaces or special characters/punctuation (underscore is exception) in names.

[PPat]

Thank you again for your response, but it says it's an "Invalid use of the Me keyword", any ideas? But to play with it, I tried changing to:

"[Student ID]=" & [90-Hr Sheet Request].StudentIDTextBox

but it gave me "Compile Error: External Name Not Defined". Here is my full code as it is now:

[CODE]
DoCmd.SetWarnings False
DoCmd.OpenQuery "Append from Request to PU (Step 1 to 2)", acViewNormal, acEdit
DoCmd.OpenForm "90-Hr Waiting PU Form", acNormal, "", "[Student ID]=" & [90-Hr Sheet Request Form].StudentIDTextBox, acEdit, acNormal
DoCmd.OpenQuery "Delete Query (from Request)", acViewNormal, acEdit
DoCmd.Close acForm, "90-Hr Sheet Request Form"
[CODE/]

[June7]

Note that ending code tag has / at beginning, not end.

Where is the code placed and where is the textbox? Try:

"[Student ID]=" & Forms.[90-Hr Sheet Request].StudentIDTextBox

Is StudentID a number field type?

[PPat]

Yes, Student ID is a number field type. And just tried that, and (on Access itself, not on the VBA module for once) I got the message "Object doesn't support this property or method". We're getting close, but I'd appreciate a little more help if thats ok haha (thank you June7).

[June7]

Then answer the other question posed in previous post. I don't know your database, although the form names sound vaguely familiar.

[PPat]

I decided to attach my Database as it is so that you can take a look at it (its only 1.2mb). Hopefully this will help!90 Hr DB.zip

[June7]

Part of issues with this database is all the 'moving' of data between tables. Never seen one with this much moving. I wouldn't do this. I would instead do one of the following:

1. Change a status field in table

2. Have a child table to document each change in status, like a history, each change would be a record with a date and action code and studentID

This is definitely not a truly relational database. If it were, this 'moving' would not be necessary.

Having said all that, issue with the code appears to be that the form name has the word 'Form' included as shown in your post but the actual code does not.