Conditional Record and Form Open Comman (1 Viewer)

[TerribadCoder]

Back again with another good one. I need to open a form based on multiple fields in a single record. I was thinking maybe Dcount could do this, but am unsure how to write it... For instance.

If the value of field "ID" AND "text33" is in table [tblVote] Then
Goto that record in a Form
Else
DoCmd.OpenForm "Voting Form", , , "ID=" & Me.ID
End If

Thanks all!

 

[pbaldy]

If both are numeric

DoCmd.OpenForm "Voting Form", , , "ID=" & Me.ID & " And OtherFieldName = " & Me.Text33

 

[TerribadCoder]

Thanks pbaldy for the quick reply. The RequestID field is numeric, however text33 and Person are text.

Additionally, I'm looking for the Condition statement (in red below) as well. Basically, if that record exists, then go to it, if not, else create a new record based on ID. I have the latter part figured out. Just not the comparison.

If the value of field "ID" AND "text33" is in table [tblVote] Then
Goto that record in a Form

Thank you!

 

[pbaldy]

For the second one being text:

DoCmd.OpenForm "Voting Form", , , "ID=" & Me.ID & " And OtherFieldName = '" & Me.Text33 & "'"

You can use a DCount() with the same criteria to test first.

 

[TerribadCoder]

The Dcount works perfectly! Thank you pbaldy!
but.... The plot thickens.

When clicking the button now, it asks for a PersonID parameter. The ID field is being brought up properly, however the form being opened has a subform for the PersonID table.

Any thoughts?

 

[pbaldy]

Not sure I understand, as I don't see that field, but perhaps you need to refer to the subform?

http://www.mvps.org/access/forms/frm0031.htm

 

[TerribadCoder]

That's exactly what I did. I referred to the subform, and used the above methodology. I found some strange behavior, though. Which made it "work". Behavior as follows.

Whenever the subform opened, it opened to a new record, even when using..

DoCmd.OpenForm "Votetbl subform", , , "[ID]=" & Me.ID & " And [PersonID] ='" & Me.Text152 & "'"

Here's where it got even stranger; I found (by accident of clicking the button twice when the subform was open) if I put the above command twice, it went to where I wanted it to go. So that is what is currently on the button, since it "works".

 

[pbaldy]

Something isn't right, as you shouldn't have to click twice. Can you post the db here, or a sample that does this?