Query based on three variables

[JST2017]

Hello Accessforums users,

I am relatively new to MS Access, and I am looking for best practices so I don't for any bad habits.

Currently I am looking open a form from a query based on three variables that can potentially result in no values. Which brings me to my two questions.

1). What is the best way to handle the situation in which no value is returned? I would ideally like to open a form to a new record with the variables they selected on the form.

2). My table records are based on the three variables. If they match, meaning there is a record available, I would like to "Up rev" the previous record. Essentially copying the previous records fields increasing the revision level.



Example of the table:

RecordID	Variable1	Variable2	Variable3	Revision	Value1	Value2	Value3
1	x	y	z	1	1	12	13
2	x	y	z	2

Continuing question #2...
So I would input my variables: x,y,z. This would match the previous revision, so I would get a "match", copy record 1, and up rev. So my form would show me the values and allow me to edit, but I do not want to be able to edit revision 1, so I create a copy and up rev it.

I am sorry if this doesn't make sense, but I do not have enough access knowledge yet to put it in better terms...

Thank you in advance for any help!

[ranman256]

Your query should show records,if any.
if not, open the form to add. (Using a Find button)

Sub btnFind_click()
Dim lCount as long

lCount= Dcount("*","qsFindQry")
if lCount=0 then
    Docmd.openForm "frmExistingRecs", ,,,acFormAdd.         'Add new
else
    Docmd.openForm "frmExistingRecs"     'Continuous form using qsFindQry
end if
end sub

[JST2017]

ranman,

Thank you that works perfectly.

In regards to the populating the form with the variables, how can I pass those variables?

[ranman256]

What variables?
if the form is open,you can access the objects.
you can pass vars to a subroutine with arguments.

Usage:
GetMyDAta 21,"bob"

Sub GetMyData ( pvNum, pvName)
  MsgBox pvName,,pvNum

end sub

[JST2017]

That is the closest thing I could find to a facepalm. Thank you ranman!