OPEN FORM TO CERTAIN RECORD

[SGTSweety]

Utilizing a command button for each record in a continuous form. Want to have command button open another form view complete details for this particular line item based on plat reference. I use this line:

DoCmd.OpenForm "frmComplete", , , Forms!frmComplete!_
frmCtxt_PlatRef.Value = frmResult_STtxt_PlatRef

where frmResult_STtxt_PlatRef is from current form and shows correct information when in 'debug' mode.

frmCtxt_PlatRef common link for each table.

No relationships exist.

Tried reversing order, with and without .value extension.

Form frmCompete opens if just "frmCompelte" in DoCmd.OpenForm operation.

I'm sure this my error. Any ideas anyone.

Thanks so much in advance.

Steven
Don't ask permission - just do it.

 

[Remou]

You need something like:
DoCmd.OpenForm "frmComplete", , ,"ID=" & Me. frmResult_STtxt_PlatRef

Where ID is the name of the field that contains the data found in a textbox called frmResult_STtxt_PlatRef on the current (sub)form.

 

[SGTSweety]

First thank you for taking the time to help.

Copy and pasted your advise in and got the "Enter Parameter Value" box.

I checked to make sure all my labels and naming were correct.

What am I missing?

Thanks,
Steven
An American

 

[SGTSweety]

I should have mentioned the "Enter Parameter Value" box was for "ID".

 

[Remou]

It is necessary to change ID to the name of an appropriate link field. That is, what ever field contains the information that appears in frmResult_STtxt_PlatRef on the current (sub)form.

 

[SGTSweety]

Thanks for the quick response.

I did as you suggested and got the same result, except this time for 'frmCtxt_PlatRef'

 

[PHV]

ID is the name of the field in the underlaying query, not the form's control name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

The end bit:
"ID=" & Me.frmResult_STtxt_PlatRef
Is a Where statement. The general idea is that you are saying:
Where Some_Field_In_The_Form_Tpen = Me.Some_Control_On_The_Form_This_Is_Running_On

At a guess, frmResult_STtxt_PlatRef is not the name of a suitable control. I thought it was when you said:
where frmResult_STtxt_PlatRef is from current form and shows correct information when in 'debug' mode.

You have a command button for each record of the subform, do you not? Try using a message box in the code for the command button, to see what is happening:
MsgBox "The current record is: " & Me.Some_Control_On_The_Form_This_Is_Running_On

 

[SGTSweety]

Sorry for the delay in thanking you. My son was sick, and believe it or not, I don't have affordable internet access in my county.

First thanks for all your help.

I changed frmfrmResult_STtxt_PlatRef [forms control name] to PlatRef [the underlying query field name] and the correct form opens without VBA error [yippie!]. However the form is blank [boo!]. Blank in that nothing is visible. Not the record select at the bottom, none of the text boxes or their labels, etc. Just the background. Had this happen before, and for the life of me can't remember what I did to resolve the problem. Took the suggest and ran MSGBOX which showed the form should be opening to right reference number. Tried a one-to-one relationship between the plat reference in the underlying queries of both forms without success - still got the form with just the background.

DoCmd.OpenForm "frmComplete", , , "PlatRef=" & _
Me.frmResult_STtxt_PlatRef
MsgBox "The current record is: " & _
Me.frmResult_STtxt_PlatRef

Again thank you for patience and continued support.
Any ideas how to remove 'programer' from my profile? I'm beginning to feel real stupid.

Steven Boyer

 

[Remou]

I think you will find that the message box is not showing a value for frmResult_STtxt_PlatRef.
Just guessing, I would say that the name of the control you want is also PlatRef. So, to continue guessing:
DoCmd.OpenForm "frmComplete", , , "PlatRef=" & _
Me.PlatRef
MsgBox "The current record is: " & _
Me.PlatRef

A good way to check is to type Me.P and see if intellisense lists PlatRef as an option, if not, have a look and see what the control that has PlatRef in it is called.

It is quite difficult to find stuff out without a good internet connection, you have my sympathy.

 

[SGTSweety]

Remou:

Actually, frmResultSTtxt_PlatRef [control name from my starting form] displays the desired data.

replacing frmResult_STtxt_PlatRef with Me.P results in 'method or data member not found'. The code below appears to be working, but for some reason the return is this complete blank form. The fact that even the labels are not visible really throws me.

The plat reference for both forms can be traced back to the main database. This reference is built on entering data and the forms just copy it from the main database.



DoCmd.OpenForm "frmComplete", , , "PlatRef=" & Me.frmResult_STtxt_PlatRef
MsgBox "The current record is: " & Me.frmResult_STtxt_PlatRef

Again, thanks for everything.

Steven Boyer

 

[SGTSweety]

Found this thread which seems to deal with blank returns.

Thread = thread702-785801

Again, I do not have any relationships at this time.

Steven

 

[Remou]

Access will return a blank for if a record is not found and Allow Additions is set to No. Is PlatRef a text field? If so, you need quotes:
[tt]DoCmd.OpenForm "frmComplete", , , "PlatRef='" & Me.frmResult_STtxt_PlatRef & "'"[/tt]
The message box is just there for purposes of testing, if it is showing the correct data, you can get rid of it.
If you look at my post again, you will find that I was suggesting that you type M.P to see what is returned by intellisense, not that you should use Me.P

 

[SGTSweety]

That did it.

I grovel before the masters.


Thanks Remou/PHV. You made my weekend.

And on that, you all have a greater weekend.

Steven Boyer
One very satisfied lowly Access programmer.