form from from

[smartslingaro]

Hi,
I have a form that I want to open based on record in current form. 2nd form is different table, linked by primary key in first. Need form to open with primary key on first form, like an autofill of the field. Keep getting a syntax error with this code:

Private Sub Command121_Click()
do.cmd.openform "Progress Note", acNormal,,"[Student ID]='"&Me[Student ID]&"'"
End Sub

Please help!

 

[Dreamweaver]

do.cmd replace with DoCmd

 

[smartslingaro]

Do the capitals matter in general? that didn't work. The first line is highlighted by the debugger.

 

[theDBguy]

Hi. Is [Student ID] a Text field? Just curious...


PS. Also, to avoid potential issues when coding, try to avoid using spaces in object names.

 

[smartslingaro]

no it is an auto number primary key on the first form and linked to second form/table by same field/number

 

[Dreamweaver]

"[Student ID]=" & Me![Student ID]

 

[theDBguy]

no it is an auto number primary key on the first form and linked to second form/table by same field/number

In that case, try removing the extra quotes. For example:

DoCmd.OpenForm "Progress Note", , ,"[Student ID]="& Me.[Student ID]

Hope it helps...


PS. Don't forget to add the missing dot after Me.

 

[Dreamweaver]

missed the bomb on mine lol

 

[smartslingaro]

what does the acNormal mean/do?

 

[smartslingaro]

run time erro 3075
syntax error in string in query expression '[Sutdent ID]='208'.

 

[theDBguy]

what does the acNormal mean/do?

I think it means to show the report normally, as if you opened it from the Nav Pane. It's the default anyway, so I don't think you need to specify it.

 

[theDBguy]

run time erro 3075
syntax error in string in query expression '[Sutdent ID]='208'.

Hi. Can you post the new code you used? Thanks.

 

[Dreamweaver]

run time erro 3075
syntax error in string in query expression '[Sutdent ID]='208'.

Post your updated code please
I'll leave you with theDbGuy He's much better than me and quicker lol

 

[smartslingaro]

Private Sub Command124_Click()
DoCmd.OpenForm "Progress Note", , ,"[Student ID]="& Me.[Student ID]

This opens the form, but does not link it with student id

 

[smartslingaro]

The progress note has it's own primary key, and I want the student id to prepopulate based on form where it clicked from. Maybe I set form to open to new record and this won't work? no, need it to be new record, just with student id prepopulated from previous screen.. hmmm....

 

[theDBguy]

The progress note has it's own primary key, and I want the student id to prepopulate based on form where it clicked from. Maybe I set form to open to new record and this won't work? no, need it to be new record, just with student id prepopulated from previous screen.. hmmm....

Hi. If I understand it correctly, you are talking about setting the Default Value correct? If so, there are two possible approaches you could consider. You could either use the OpenArgs argument to pass the ID, or use a form/subform setup for the second form and open it using the code we gave you.

 

[Dreamweaver]

Have you got data entry set to no

 

[Pat Hartman]

Private Sub Command124_Click()
DoCmd.OpenForm "Progress Note", , ,"[Student ID]="& Me.[Student ID]

This opens the form, but does not link it with student id

You are correct. If you use a subform, Access will automatically populate the foreign key as long as the Master/Child links are properly set. With a PopUp form, you're on your own. The two forms are not connected in any way so Access has no way to know what you mean.

I use the OpenArgs to pass in the PK that needs to be used as the FK on this form.

DoCmd.OpenForm "frmProgressNote", , , , , acDialog, Me.StudentID

Then in the BeforeInsert event of the progress note form:

Me.StudentID = Me.OpenArgs

Using the BeforeInsert event will ensure that you do NOT dirty the record in the pop up form. You want the user to type at least one character so his intent to make a record is clear before you populate the FK.

PS:
1. using acDialog ensures that the user finishes the record before returning to the original form. You don't want multiple windows to be available except in special cases.
2. I took the liberty of renaming your objects using Good Practice names.