Popup Form

[enrico1982]

I have created a button that opens a related record from a subform, like the
'See Product Details' button in the Access 2002 sample database 'orders'. I
used the following code:

Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

strDocName = "FaultReportForm"
strLinkCriteria = "Date = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

The problem is my subform represents a union query which draws from a few
different forms 'FaultReportForm' is just one of them. Is there a bit of code
i could use so it can open the correct form out of a few forms.

Also the above code doesn't work on the first click, it shows a blank
record, it only shows the corrent record if the form is already open.

Any help much appreciated

Thanks in advance.

 

[Dirk Goldgar]

I have created a button that opens a related record from a subform,
like the 'See Product Details' button in the Access 2002 sample
database 'orders'. I used the following code:

Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

strDocName = "FaultReportForm"
strLinkCriteria = "Date = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

The problem is my subform represents a union query which draws from a
few different forms 'FaultReportForm' is just one of them. Is there a
bit of code i could use so it can open the correct form out of a few
forms.

Correction: your union query draws from different *tables*, not
different *forms*. Forms don't contain data; they're just windows into
the tables..

Also the above code doesn't work on the first click, it shows a blank
record, it only shows the corrent record if the form is already open.

Any help much appreciated

Thanks in advance.

I'm not entirely sure I've understood your setup. For your first
question,

The problem is my subform represents a union query which draws from a
few different forms 'FaultReportForm' is just one of them. Is there a
bit of code i could use so it can open the correct form out of a few
forms.

how would the code know which form it is that should be opened. Does
your UnitDataSubq subform contain a field that could be used to
determine this? If so, you might revise your code along these lines:

'----- start of example code -----
Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

' Assuming the field "TestName" can be used to determine
' which form to open.
Select Case Me.TestName
Case "TestX" : strDocName = "FaultReportForm"
Case "TestY" : strDocName = "SomeOtherForm"
' ...
End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub
'----- end of example code -----

For your second question,

Also the above code doesn't work on the first click, it shows a blank
record, it only shows the corrent record if the form is already open.

I'm not sure which form you're referring to. Is the form "UnitData"
open at the time this button is clicked? I suppose it may be that the
problem comes from the missing brackets in your criteria-expression --
you need square brackets around "Date", as I've placed above, to avoid
having it be confused with the Date() function.

 

[enrico1982]

Thanks for the suggestions Dirk,

Have tried the code you suggested and not having much luck (i am a visual
basic newbie!).

Access doesn't like the first bit:

Select Case Me.TestName

It highlights TestName and gives a compile error saying "Method or data
member not found". I have made sure the field is correctly spelt.

enrico

I have created a button that opens a related record from a subform,
like the 'See Product Details' button in the Access 2002 sample
database 'orders'. I used the following code:

Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

strDocName = "FaultReportForm"
strLinkCriteria = "Date = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

The problem is my subform represents a union query which draws from a
few different forms 'FaultReportForm' is just one of them. Is there a
bit of code i could use so it can open the correct form out of a few
forms.

Correction: your union query draws from different *tables*, not
different *forms*. Forms don't contain data; they're just windows into
the tables..

Also the above code doesn't work on the first click, it shows a blank
record, it only shows the corrent record if the form is already open.

Any help much appreciated

Thanks in advance.

I'm not entirely sure I've understood your setup. For your first
question,

The problem is my subform represents a union query which draws from a
few different forms 'FaultReportForm' is just one of them. Is there a
bit of code i could use so it can open the correct form out of a few
forms.

how would the code know which form it is that should be opened. Does
your UnitDataSubq subform contain a field that could be used to
determine this? If so, you might revise your code along these lines:

'----- start of example code -----
Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

' Assuming the field "TestName" can be used to determine
' which form to open.
Select Case Me.TestName
Case "TestX" : strDocName = "FaultReportForm"
Case "TestY" : strDocName = "SomeOtherForm"
' ...
End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub
'----- end of example code -----

For your second question,

Also the above code doesn't work on the first click, it shows a blank
record, it only shows the corrent record if the form is already open.

I'm not sure which form you're referring to. Is the form "UnitData"
open at the time this button is clicked? I suppose it may be that the
problem comes from the missing brackets in your criteria-expression --
you need square brackets around "Date", as I've placed above, to avoid
having it be confused with the Date() function.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the suggestions Dirk,

Have tried the code you suggested and not having much luck (i am a
visual basic newbie!).

Access doesn't like the first bit:

Select Case Me.TestName

It highlights TestName and gives a compile error saying "Method or
data member not found". I have made sure the field is correctly spelt.

enrico

I have created a button that opens a related record from a subform,
like the 'See Product Details' button in the Access 2002 sample
database 'orders'. I used the following code:

Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

strDocName = "FaultReportForm"
strLinkCriteria = "Date = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

The problem is my subform represents a union query which draws from
a few different forms 'FaultReportForm' is just one of them. Is
there a bit of code i could use so it can open the correct form out
of a few forms.

Correction: your union query draws from different *tables*, not
different *forms*. Forms don't contain data; they're just windows
into the tables..

Also the above code doesn't work on the first click, it shows a
blank record, it only shows the corrent record if the form is
already open.

Any help much appreciated

Thanks in advance.

I'm not entirely sure I've understood your setup. For your first
question,

The problem is my subform represents a union query which draws from
a few different forms 'FaultReportForm' is just one of them. Is
there a bit of code i could use so it can open the correct form out
of a few forms.

how would the code know which form it is that should be opened. Does
your UnitDataSubq subform contain a field that could be used to
determine this? If so, you might revise your code along these lines:

'----- start of example code -----
Private Sub Command36_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

' Assuming the field "TestName" can be used to determine
' which form to open.
Select Case Me.TestName
Case "TestX" : strDocName = "FaultReportForm"
Case "TestY" : strDocName = "SomeOtherForm"
' ...
End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub
'----- end of example code -----

For your second question,

Also the above code doesn't work on the first click, it shows a
blank record, it only shows the corrent record if the form is
already open.

I'm not sure which form you're referring to. Is the form "UnitData"
open at the time this button is clicked? I suppose it may be that
the problem comes from the missing brackets in your
criteria-expression -- you need square brackets around "Date", as
I've placed above, to avoid having it be confused with the Date()
function.

1. Did you change "TestName" to the name of the appropriate control?

2. This code assumes that the command button is on the subform, but
maybe it's on the main form. If so, you'd need to change this line:

to this

Select Case Me.UnitDataSubq.Form.TestName

or

Select Case Me!UnitDataSubq.Form!TestName

 

[rico]

Thanks again Dirk,

I think i might be getting somewhere, but still not sorted.

The code just runs the msgbox and so assuming that the Len(strDocName) = 0
even when one of the records is selected. The code currently looks like this:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler


Select Case Me!UnitDataSubQ.Form!RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

End Sub

 

[Dirk Goldgar]

Thanks again Dirk,

I think i might be getting somewhere, but still not sorted.

The code just runs the msgbox and so assuming that the
Len(strDocName) = 0 even when one of the records is selected. The
code currently looks like this:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler


Select Case Me!UnitDataSubQ.Form!RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

End Sub

The code itself looks okay to me, so it seems that
Me!UnitDataSubQ.Form!RecordType doesn't contain what you think it
contains. Why not add a a display of this value for debugging purposes,
by changing this ...

MsgBox "Can't tell what form to open!"

to this:

MsgBox "Can't tell what form to open for record type '" & _
Me!UnitDataSubQ.Form!RecordType & "'!"

 

[rico]

Have done that, and it comes up saying as it should 'cant find form to open
for 'Fault Report'' or CD23 test (obviously depending on what is selected)

so i would assume that something is wrong with:

am i correct, if so, any further suggestions?

ps. have checked FaultReportForm and CD23 are spelt correctly

Thanks again Dirk,

I think i might be getting somewhere, but still not sorted.

The code just runs the msgbox and so assuming that the
Len(strDocName) = 0 even when one of the records is selected. The
code currently looks like this:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler


Select Case Me!UnitDataSubQ.Form!RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

End Sub

The code itself looks okay to me, so it seems that
Me!UnitDataSubQ.Form!RecordType doesn't contain what you think it
contains. Why not add a a display of this value for debugging purposes,
by changing this ...

MsgBox "Can't tell what form to open!"

to this:

MsgBox "Can't tell what form to open for record type '" & _
Me!UnitDataSubQ.Form!RecordType & "'!"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Have done that, and it comes up saying as it should 'cant find form
to open for 'Fault Report'' or CD23 test (obviously depending on what
is selected)

so i would assume that something is wrong with:

am i correct, if so, any further suggestions?

ps. have checked FaultReportForm and CD23 are spelt correctly

Thanks again Dirk,

I think i might be getting somewhere, but still not sorted.

The code just runs the msgbox and so assuming that the
Len(strDocName) = 0 even when one of the records is selected. The
code currently looks like this:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler


Select Case Me!UnitDataSubQ.Form!RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] =
Forms!UnitData!UnitDataSubq!Date" DoCmd.OpenForm
strDocName, , , strLinkCriteria End If

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

End Sub

The code itself looks okay to me, so it seems that
Me!UnitDataSubQ.Form!RecordType doesn't contain what you think it
contains. Why not add a a display of this value for debugging
purposes, by changing this ...

MsgBox "Can't tell what form to open!"

to this:

MsgBox "Can't tell what form to open for record type '" & _
Me!UnitDataSubQ.Form!RecordType & "'!"

I tried that code in a form/subform combination of my own and it worked
fine. So I think you must have misspelled *something*, or have extra
spaces in a name, or too few spaces. Now, when you say you get a
message that it 'cant find form to open for 'Fault Report'', you mean
that you get the message from our message box? That's not an exact
quote of the message, so I can't be sure where you're going wrong. If
you can't spot the problem, be sure you copy and paste both the code
from the form *and* the message. You should be able to press Ctrl+C
when the message box is displayed to copy the message box to the
clipboard.

 

[rico]

Dirk,

Finally got it sorted, for some reason had a space infront of the cases!

Thankyou ever so much for all your help, it has been greatly appreciated.

Thanks again

enrico

Have done that, and it comes up saying as it should 'cant find form
to open for 'Fault Report'' or CD23 test (obviously depending on what
is selected)

so i would assume that something is wrong with:

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

am i correct, if so, any further suggestions?

ps. have checked FaultReportForm and CD23 are spelt correctly

Thanks again Dirk,

I think i might be getting somewhere, but still not sorted.

The code just runs the msgbox and so assuming that the
Len(strDocName) = 0 even when one of the records is selected. The
code currently looks like this:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler


Select Case Me!UnitDataSubQ.Form!RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23 Test": strDocName = "CD23"

End Select

If Len(strDocName) = 0 Then
MsgBox "Can't tell what form to open!"
Else
strLinkCriteria = "[Date] =
Forms!UnitData!UnitDataSubq!Date" DoCmd.OpenForm
strDocName, , , strLinkCriteria End If

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description

End Sub

The code itself looks okay to me, so it seems that
Me!UnitDataSubQ.Form!RecordType doesn't contain what you think it
contains. Why not add a a display of this value for debugging
purposes, by changing this ...

MsgBox "Can't tell what form to open!"

to this:

MsgBox "Can't tell what form to open for record type '" & _
Me!UnitDataSubQ.Form!RecordType & "'!"

I tried that code in a form/subform combination of my own and it worked
fine. So I think you must have misspelled *something*, or have extra
spaces in a name, or too few spaces. Now, when you say you get a
message that it 'cant find form to open for 'Fault Report'', you mean
that you get the message from our message box? That's not an exact
quote of the message, so I can't be sure where you're going wrong. If
you can't spot the problem, be sure you copy and paste both the code
from the form *and* the message. You should be able to press Ctrl+C
when the message box is displayed to copy the message box to the
clipboard.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)