I am being asked for a value when I don't want to ....

[Ronny Sigo]

Hello all,
I have defined a public variable at module level :
Public sSchrijver As String

Then, in the first form's combobox I assign a value to it :
Private Sub Kiesschrijver_C hange()
If IsNull(Me!Kiess chrijver.Value) Then
sSchrijver = ""
Else
sSchrijver = Nz(Trim(Me!Kies schrijver.Value ))
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSchrijfbrie f"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

End Sub
Then, when the second form (frmSchrijfbrie f) opens, I define this form's
recordsource like this :

Private Sub Form_Open(Cance l As Integer)
Me.RecordSource = "select * from tblMwerkers where volledigenaam =
sSchrijver"
End Sub

When running the whole thing I am being asked for a value for sSchrijver,
like when you run a parameter query, although the value for sSchrijver has
already been defined in the Kiesschrijver_C hange() event ....

Can somebody tell me what I am doing wrong ?

Any help very much appreciated !
Thanks
Ronny Sigo

[Fletcher Arnold]

"Ronny Sigo" <ro********@sky net.be> wrote in message
news:3f******** **************@ reader2.news.sk ynet.be...

Hello all,
I have defined a public variable at module level :
Public sSchrijver As String

Then, in the first form's combobox I assign a value to it :
Private Sub Kiesschrijver_C hange()
If IsNull(Me!Kiess chrijver.Value) Then
sSchrijver = ""
Else
sSchrijver = Nz(Trim(Me!Kies schrijver.Value ))
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSchrijfbrie f"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

End Sub
Then, when the second form (frmSchrijfbrie f) opens, I define this form's
recordsource like this :

Private Sub Form_Open(Cance l As Integer)
Me.RecordSource = "select * from tblMwerkers where volledigenaam =
sSchrijver"
End Sub

When running the whole thing I am being asked for a value for sSchrijver,
like when you run a parameter query, although the value for sSchrijver has
already been defined in the Kiesschrijver_C hange() event ....

Can somebody tell me what I am doing wrong ?

Any help very much appreciated !
Thanks
Ronny Sigo

sSchrijver is a string so you need quotes around it.
Try this:

Private Sub Form_Open(Cance l As Integer)

Dim strSQL As String

strSQL = "select * from tblMwerkers where volledigenaam=" ""
strSQL = strSQL & sSchrijver & """"

Me.RecordSource = strSQL

End Sub

[Peter Doering]

Ronny,

Public sSchrijver As String
For this operation you don't need sSchrijver.

Private Sub Kiesschrijver_C hange()
Dim stDocName As String
Dim stLinkCriteria As String
If Nz(Me!Kiesschri jver.Value,"") <>"" Then
stDocName = "frmSchrijfbrie f"
stLinkCriteria = "volledigen aam = '" & _
Me!Kiesschrijve r.Value & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub
Private Sub Form_Open(Cance l As Integer)
Me.RecordSource = "select * from tblMwerkers where volledigenaam =
sSchrijver"
End Sub

Remove these lines.

HTH - Peter

--
No mails please.

[Ronny Sigo]

Thanks guys :)

"Ronny Sigo" <ro********@sky net.be> wrote in message
news:3f******** **************@ reader2.news.sk ynet.be...

Hello all,
I have defined a public variable at module level :
Public sSchrijver As String