Open a form with a filter based on dependent data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form known as "relations" and a form known as "data_entry".
data_entry's primary key is known as "ID", and has a bunch of other fields.
relations is based on a different table and only has 3 fields: The Primary
key, "current" and "related".

What I would like to do is for a given record in data_entry, pull up
"relations", but only show the records that has the data in the "current"
field match what's in the "ID" field of the data_entry form. The idea behind
this is that if there are issues connected to a given record in data_entry, I
would like to be able to store other "ID" numbers so that the users know
where to go for more information.
I have posted what I have below, but I keep getting a "2501 error". Any idea
why this is?

Thanks!

Private Sub Command200_Click() 'This is the relations button
Dim holdval As String

If Me.Dirty Then 'Save any edits.

Me.Dirty = False

End If

If Me.NewRecord Then 'Check if new

MsgBox "The record must be saved before relations are allowed"

Else

holdval = "[current] = " & Me.[ID]

DoCmd.OpenForm "relations", acNormal, , holdval
End If

End Sub
 
Try not assigning your "where condition" to a variable, just olace the
criteria in the line with the statement.

change the line:
DoCmd.OpenForm "relations", acNormal, , holdval

to:
DoCmd.OpenForm "relations", acNormal, , "[current] = Me.[ID]"

If you want to use a variable then assign the value of Me.[ID] to the
variable and then use the variable in the Where Condition: "[current] = " &
holdval & ""

The statement above assumes that the holdval variable is a number and not a
string value.
 
Thanks Mr B, it looks like I'm almost there.
When I try your suggestion to use the line:
DoCmd.OpenForm "relations", acNormal, , "[current] = Me.[ID]"

I run the command, and I am prompted in a window called "Enter parameter
value"
it then asks for me.ID , which I thought I had already defined. Am I missing
something?
Thanks

Mr B said:
Try not assigning your "where condition" to a variable, just olace the
criteria in the line with the statement.

change the line:
DoCmd.OpenForm "relations", acNormal, , holdval

to:
DoCmd.OpenForm "relations", acNormal, , "[current] = Me.[ID]"

If you want to use a variable then assign the value of Me.[ID] to the
variable and then use the variable in the Where Condition: "[current] = " &
holdval & ""

The statement above assumes that the holdval variable is a number and not a
string value.

--
HTH

Mr B


AndyM said:
I have a form known as "relations" and a form known as "data_entry".
data_entry's primary key is known as "ID", and has a bunch of other fields.
relations is based on a different table and only has 3 fields: The Primary
key, "current" and "related".

What I would like to do is for a given record in data_entry, pull up
"relations", but only show the records that has the data in the "current"
field match what's in the "ID" field of the data_entry form. The idea behind
this is that if there are issues connected to a given record in data_entry, I
would like to be able to store other "ID" numbers so that the users know
where to go for more information.
I have posted what I have below, but I keep getting a "2501 error". Any idea
why this is?

Thanks!

Private Sub Command200_Click() 'This is the relations button
Dim holdval As String

If Me.Dirty Then 'Save any edits.

Me.Dirty = False

End If

If Me.NewRecord Then 'Check if new

MsgBox "The record must be saved before relations are allowed"

Else

holdval = "[current] = " & Me.[ID]

DoCmd.OpenForm "relations", acNormal, , holdval
End If

End Sub
Click to expand...
Click to expand...
 
Back
Top