Filter a filtered pop-up form from a command button

M

MaryF

Hello,

I am having problems with trying to filter a filtered pop-up form.

I have a command button cmdNetReq on a main form frm_System that opens a
related record in frm_NetRequirements. On the pop-up frm_NetRequirements, I
have 4 NetType check boxes/yes-no fields: NetReq_Operator,
NetReq_Maintainer, NetReq_Trainer, NetReq_NoTraining. Next to three of those
check boxes I have command buttons where the user can click to view details
for that specific training type for the system.

These are 2K one-to-many relationships:

tbl_System (SysID) PK and tbl_NetRequirements (NetReq_SystemID) FK
tbl_NetType (NetTypeID) PK and tbl_NetRequirements (NetReq_NetTypeID) FK

The code below ends up giving me all of the records in the database that are
for Train the Trainer class, instead of only the one record for that filtered
system. And I am getting a “Type mismatch†error when there is no related
Train the Trainer record. Can anyone please help me with this code? I hope I
have given you enough information.

Private Sub cmdTrainer_Click() ‘ this command button is on
frm_NetRequirements
' Shows the filtered data for the Train the Trainer Training
On Error GoTo Err_cmdTrainer_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String

stDocName = "frm_NetRequirements"
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]

If (NetReq_NetTypeID) = Null Then
strMsg = NewData & "There is no training information for this system,
would you like to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, ,
Me!NetReq_SystemID
Case vbNo
Exit Sub
End Select
Else
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
Me.FilterOn = True
DoCmd.OpenForm stDocName, , , , acFormAdd, , stLinkCriteria
End If
Exit_cmdTrainer_Click:
Exit Sub
Err_cmdTrainer_Click:
MsgBox Err.Description
Resume Exit_cmdTrainer_Click
End Sub

Thank you very much,
Mary
 
G

Gerald Stanley

Here are a couple of suggestions for code changes

1 - The line
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]
would not work as the identifier before the = sign should be a column on the
form's Record Source. So try
stLinkCriteria = "NetReq_SystemID = " & Me![NetReq_SystemID]

2 - The line
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
appears to have the second " in the wrong place. Should it be
Me.Form.Filter = "[NetReq_NetTypeID] = 3 And " & stLinkCriteria

Hope This Helps
Gerald Stanley MCSD
 
M

MaryF

Hi Gerald,

Thank you very much for your reply! That fixed the problem of flitering the
filtered form. Thank you! However, I am still having problems when there is
no related record and the user wants to add one. If the system does not have
a train-the-trainer record, I get an error saying " Cannot add or change
record because a related record is required in tbl_System." I am also not
getting the strMsg string when there is no related record.

I need to tell you that I have the below code in the On Load event of
frm_NetRequirements, so when a new record is made it is set to match the main
SysID on tbl_System. This works perfectly for adding new NetReq records:

Private Sub Form_Load()
' Sets the new entry form to the current system ID
' txtRelSysID is the name of NetReq_SystemID
Me!txtRel_SysID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub

I think I need to do something like this for the new training records too,
but I am not sure where and how to tell it to link the new record to the
current NetReq_SystemID/SysID.
What do you suggest?

Thank you again,
Mary

Gerald Stanley said:
Here are a couple of suggestions for code changes

1 - The line
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]
would not work as the identifier before the = sign should be a column on the
form's Record Source. So try
stLinkCriteria = "NetReq_SystemID = " & Me![NetReq_SystemID]

2 - The line
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
appears to have the second " in the wrong place. Should it be
Me.Form.Filter = "[NetReq_NetTypeID] = 3 And " & stLinkCriteria

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hello,

I am having problems with trying to filter a filtered pop-up form.

I have a command button cmdNetReq on a main form frm_System that opens a
related record in frm_NetRequirements. On the pop-up frm_NetRequirements, I
have 4 NetType check boxes/yes-no fields: NetReq_Operator,
NetReq_Maintainer, NetReq_Trainer, NetReq_NoTraining. Next to three of those
check boxes I have command buttons where the user can click to view details
for that specific training type for the system.

These are 2K one-to-many relationships:

tbl_System (SysID) PK and tbl_NetRequirements (NetReq_SystemID) FK
tbl_NetType (NetTypeID) PK and tbl_NetRequirements (NetReq_NetTypeID) FK

The code below ends up giving me all of the records in the database that are
for Train the Trainer class, instead of only the one record for that filtered
system. And I am getting a “Type mismatch†error when there is no related
Train the Trainer record. Can anyone please help me with this code? I hope I
have given you enough information.

Private Sub cmdTrainer_Click() ‘ this command button is on
frm_NetRequirements
' Shows the filtered data for the Train the Trainer Training
On Error GoTo Err_cmdTrainer_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String

stDocName = "frm_NetRequirements"
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]

If (NetReq_NetTypeID) = Null Then
strMsg = NewData & "There is no training information for this system,
would you like to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, ,
Me!NetReq_SystemID
Case vbNo
Exit Sub
End Select
Else
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
Me.FilterOn = True
DoCmd.OpenForm stDocName, , , , acFormAdd, , stLinkCriteria
End If
Exit_cmdTrainer_Click:
Exit Sub
Err_cmdTrainer_Click:
MsgBox Err.Description
Resume Exit_cmdTrainer_Click
End Sub

Thank you very much,
Mary
Click to expand...
Click to expand...
 
G

Gerald Stanley

That problem may be fixed by replacing
If (NetReq_NetTypeID) = Null Then
with
If IsNull(NetReq_NetTypeID) Then

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hi Gerald,

Thank you very much for your reply! That fixed the problem of flitering the
filtered form. Thank you! However, I am still having problems when there is
no related record and the user wants to add one. If the system does not have
a train-the-trainer record, I get an error saying " Cannot add or change
record because a related record is required in tbl_System." I am also not
getting the strMsg string when there is no related record.

I need to tell you that I have the below code in the On Load event of
frm_NetRequirements, so when a new record is made it is set to match the main
SysID on tbl_System. This works perfectly for adding new NetReq records:

Private Sub Form_Load()
' Sets the new entry form to the current system ID
' txtRelSysID is the name of NetReq_SystemID
Me!txtRel_SysID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub

I think I need to do something like this for the new training records too,
but I am not sure where and how to tell it to link the new record to the
current NetReq_SystemID/SysID.
What do you suggest?

Thank you again,
Mary

Gerald Stanley said:
Here are a couple of suggestions for code changes

1 - The line
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]
would not work as the identifier before the = sign should be a column on the
form's Record Source. So try
stLinkCriteria = "NetReq_SystemID = " & Me![NetReq_SystemID]

2 - The line
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
appears to have the second " in the wrong place. Should it be
Me.Form.Filter = "[NetReq_NetTypeID] = 3 And " & stLinkCriteria

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hello,

I am having problems with trying to filter a filtered pop-up form.

I have a command button cmdNetReq on a main form frm_System that opens a
related record in frm_NetRequirements. On the pop-up frm_NetRequirements, I
have 4 NetType check boxes/yes-no fields: NetReq_Operator,
NetReq_Maintainer, NetReq_Trainer, NetReq_NoTraining. Next to three of those
check boxes I have command buttons where the user can click to view details
for that specific training type for the system.

These are 2K one-to-many relationships:

tbl_System (SysID) PK and tbl_NetRequirements (NetReq_SystemID) FK
tbl_NetType (NetTypeID) PK and tbl_NetRequirements (NetReq_NetTypeID) FK

The code below ends up giving me all of the records in the database that are
for Train the Trainer class, instead of only the one record for that filtered
system. And I am getting a “Type mismatch†error when there is no related
Train the Trainer record. Can anyone please help me with this code? I hope I
have given you enough information.

Private Sub cmdTrainer_Click() ‘ this command button is on
frm_NetRequirements
' Shows the filtered data for the Train the Trainer Training
On Error GoTo Err_cmdTrainer_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String

stDocName = "frm_NetRequirements"
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]

If (NetReq_NetTypeID) = Null Then
strMsg = NewData & "There is no training information for this system,
would you like to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, ,
Me!NetReq_SystemID
Case vbNo
Exit Sub
End Select
Else
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
Me.FilterOn = True
DoCmd.OpenForm stDocName, , , , acFormAdd, , stLinkCriteria
End If
Exit_cmdTrainer_Click:
Exit Sub
Err_cmdTrainer_Click:
MsgBox Err.Description
Resume Exit_cmdTrainer_Click
End Sub

Thank you very much,
Mary
Click to expand...
Click to expand...
Click to expand...
 
M

MaryF

Hi Gerald,

Thanks again for the quick response! :0) Unfortunately, I am still getting
the same error and the StrMsg is not coming up when there is no related
record. I do get a new blank record, but the NetReq_SystemID and
NetReq_NetTypeID is not being populated automatically, therefore I cannot
save the new record.

Do you have any other suggestions? I can send you a smaller version of my
database if you want....

Thank you for all of your time,
Mary :)

Gerald Stanley said:
That problem may be fixed by replacing
If (NetReq_NetTypeID) = Null Then
with
If IsNull(NetReq_NetTypeID) Then

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hi Gerald,

Thank you very much for your reply! That fixed the problem of flitering the
filtered form. Thank you! However, I am still having problems when there is
no related record and the user wants to add one. If the system does not have
a train-the-trainer record, I get an error saying " Cannot add or change
record because a related record is required in tbl_System." I am also not
getting the strMsg string when there is no related record.

I need to tell you that I have the below code in the On Load event of
frm_NetRequirements, so when a new record is made it is set to match the main
SysID on tbl_System. This works perfectly for adding new NetReq records:

Private Sub Form_Load()
' Sets the new entry form to the current system ID
' txtRelSysID is the name of NetReq_SystemID
Me!txtRel_SysID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub

I think I need to do something like this for the new training records too,
but I am not sure where and how to tell it to link the new record to the
current NetReq_SystemID/SysID.
What do you suggest?

Thank you again,
Mary

Gerald Stanley said:
Here are a couple of suggestions for code changes

1 - The line
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]
would not work as the identifier before the = sign should be a column on the
form's Record Source. So try
stLinkCriteria = "NetReq_SystemID = " & Me![NetReq_SystemID]

2 - The line
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
appears to have the second " in the wrong place. Should it be
Me.Form.Filter = "[NetReq_NetTypeID] = 3 And " & stLinkCriteria

Hope This Helps
Gerald Stanley MCSD

:

Hello,

I am having problems with trying to filter a filtered pop-up form.

I have a command button cmdNetReq on a main form frm_System that opens a
related record in frm_NetRequirements. On the pop-up frm_NetRequirements, I
have 4 NetType check boxes/yes-no fields: NetReq_Operator,
NetReq_Maintainer, NetReq_Trainer, NetReq_NoTraining. Next to three of those
check boxes I have command buttons where the user can click to view details
for that specific training type for the system.

These are 2K one-to-many relationships:

tbl_System (SysID) PK and tbl_NetRequirements (NetReq_SystemID) FK
tbl_NetType (NetTypeID) PK and tbl_NetRequirements (NetReq_NetTypeID) FK

The code below ends up giving me all of the records in the database that are
for Train the Trainer class, instead of only the one record for that filtered
system. And I am getting a “Type mismatch†error when there is no related
Train the Trainer record. Can anyone please help me with this code? I hope I
have given you enough information.

Private Sub cmdTrainer_Click() ‘ this command button is on
frm_NetRequirements
' Shows the filtered data for the Train the Trainer Training
On Error GoTo Err_cmdTrainer_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String

stDocName = "frm_NetRequirements"
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]

If (NetReq_NetTypeID) = Null Then
strMsg = NewData & "There is no training information for this system,
would you like to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, ,
Me!NetReq_SystemID
Case vbNo
Exit Sub
End Select
Else
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
Me.FilterOn = True
DoCmd.OpenForm stDocName, , , , acFormAdd, , stLinkCriteria
End If
Exit_cmdTrainer_Click:
Exit Sub
Err_cmdTrainer_Click:
MsgBox Err.Description
Resume Exit_cmdTrainer_Click
End Sub

Thank you very much,
Mary
Click to expand...
Click to expand...
Click to expand...
 
G

Gerald Stanley

I can't spot any obvious coding problems so you may need to examine the
application logic.
If the IsNull() test is not true when there are no associated records, then
either a) the test itself is not the right one for detecting that particular
condition or b) you are applying the test too early.

If it is option a), then you could try replacing the ISNull() with a DCount
() test that would yield 0 if there were no matching records.

If it is option b), then you could try moving the code that filters the form
so that it occurs prior to your IsNull test.

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hi Gerald,

Thanks again for the quick response! :0) Unfortunately, I am still getting
the same error and the StrMsg is not coming up when there is no related
record. I do get a new blank record, but the NetReq_SystemID and
NetReq_NetTypeID is not being populated automatically, therefore I cannot
save the new record.

Do you have any other suggestions? I can send you a smaller version of my
database if you want....

Thank you for all of your time,
Mary :)

Gerald Stanley said:
That problem may be fixed by replacing
If (NetReq_NetTypeID) = Null Then
with
If IsNull(NetReq_NetTypeID) Then

Hope This Helps
Gerald Stanley MCSD

MaryF said:
Hi Gerald,

Thank you very much for your reply! That fixed the problem of flitering the
filtered form. Thank you! However, I am still having problems when there is
no related record and the user wants to add one. If the system does not have
a train-the-trainer record, I get an error saying " Cannot add or change
record because a related record is required in tbl_System." I am also not
getting the strMsg string when there is no related record.

I need to tell you that I have the below code in the On Load event of
frm_NetRequirements, so when a new record is made it is set to match the main
SysID on tbl_System. This works perfectly for adding new NetReq records:

Private Sub Form_Load()
' Sets the new entry form to the current system ID
' txtRelSysID is the name of NetReq_SystemID
Me!txtRel_SysID.DefaultValue = Nz(Me.OpenArgs, "")
End Sub

I think I need to do something like this for the new training records too,
but I am not sure where and how to tell it to link the new record to the
current NetReq_SystemID/SysID.
What do you suggest?

Thank you again,
Mary

:

Here are a couple of suggestions for code changes

1 - The line
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]
would not work as the identifier before the = sign should be a column on the
form's Record Source. So try
stLinkCriteria = "NetReq_SystemID = " & Me![NetReq_SystemID]

2 - The line
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
appears to have the second " in the wrong place. Should it be
Me.Form.Filter = "[NetReq_NetTypeID] = 3 And " & stLinkCriteria

Hope This Helps
Gerald Stanley MCSD

:

Hello,

I am having problems with trying to filter a filtered pop-up form.

I have a command button cmdNetReq on a main form frm_System that opens a
related record in frm_NetRequirements. On the pop-up frm_NetRequirements, I
have 4 NetType check boxes/yes-no fields: NetReq_Operator,
NetReq_Maintainer, NetReq_Trainer, NetReq_NoTraining. Next to three of those
check boxes I have command buttons where the user can click to view details
for that specific training type for the system.

These are 2K one-to-many relationships:

tbl_System (SysID) PK and tbl_NetRequirements (NetReq_SystemID) FK
tbl_NetType (NetTypeID) PK and tbl_NetRequirements (NetReq_NetTypeID) FK

The code below ends up giving me all of the records in the database that are
for Train the Trainer class, instead of only the one record for that filtered
system. And I am getting a “Type mismatch†error when there is no related
Train the Trainer record. Can anyone please help me with this code? I hope I
have given you enough information.

Private Sub cmdTrainer_Click() ‘ this command button is on
frm_NetRequirements
' Shows the filtered data for the Train the Trainer Training
On Error GoTo Err_cmdTrainer_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim NewData As String
Dim strMsg As String
Dim mbrResponse As String

stDocName = "frm_NetRequirements"
stLinkCriteria = "Forms![frm_System].Form![SysID] =" &
Me![NetReq_SystemID]

If (NetReq_NetTypeID) = Null Then
strMsg = NewData & "There is no training information for this system,
would you like to add this information?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "New Data")
Select Case mbrResponse
Case vbYes
'Add new data"
DoCmd.OpenForm stDocName, , , , acFormAdd, ,
Me!NetReq_SystemID
Case vbNo
Exit Sub
End Select
Else
Me.Form.Filter = "[NetReq_NetTypeID]=" & 3 And stLinkCriteria
Me.FilterOn = True
DoCmd.OpenForm stDocName, , , , acFormAdd, , stLinkCriteria
End If
Exit_cmdTrainer_Click:
Exit Sub
Err_cmdTrainer_Click:
MsgBox Err.Description
Resume Exit_cmdTrainer_Click
End Sub

Thank you very much,
Mary
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tab Control Problem (Access 2K) 8
Filter using a Command Button 4
Command Button not working on form - HELP!!! 3
Open form with Command button Error 3
form filter and coomand button 0
Command Button to Find and Filter Records 1
Link criteria on Open Form Command Button 5
command button to open form needs to assign values 1

Top