Open form using where clause

[keri]

Hi,

Simple version of the question.....

How do I use the where clause of the open form command to show an
account with a matching ID to be displayed when the form is opened?

Eg. I select a record on a continuous form and click and edit button.
The button runs the open form code. I want the APPID of the current
record on the continous form to match the APPID of the record viewed
on the new form to be opened.

Complex version of the question;

I have a form - FRMAPPOINTMENTS with entries stored in a table,
TBLAPPOINTMENTS. I have a query based on this table QRYAPPOINTMENTS,
and a continuous form based on this query - FRMQUERYAPPOINTMENTS.
(There is some (probably flawed!) logic behind this bizarre setup).

I want to select a record in the continous form FRMQUERYAPPOINTMENTS
and by clicking on an edit button on this form to open the form
FRMAPPOINTMENTS at the corresponding record. The matching fields are
the APPID in both forms.

I have tried numerous where clauses in the openform command and having
no luck.

Thanks,

[salad]

keri wrote:

Hi,

Simple version of the question.....

How do I use the where clause of the open form command to show an
account with a matching ID to be displayed when the form is opened?

Eg. I select a record on a continuous form and click and edit button.
The button runs the open form code. I want the APPID of the current
record on the continous form to match the APPID of the record viewed
on the new form to be opened.

Complex version of the question;

I have a form - FRMAPPOINTMENTS with entries stored in a table,
TBLAPPOINTMENTS. I have a query based on this table QRYAPPOINTMENTS,
and a continuous form based on this query - FRMQUERYAPPOINTMENTS.
(There is some (probably flawed!) logic behind this bizarre setup).

I want to select a record in the continous form FRMQUERYAPPOINTMENTS
and by clicking on an edit button on this form to open the form
FRMAPPOINTMENTS at the corresponding record. The matching fields are
the APPID in both forms.

I have tried numerous where clauses in the openform command and having
no luck.

Thanks,

var = "Smith"
"ID = """ & var & """

var = 1
"ID = " & var

var = Date
"ID = #" & var & "#"

Let's say you have a field called ID in FRMQUERYAPPOINTMENTS. The ID of
the current record is the ID number you want to open in FRMAPPOINTMENTS.
Remember, strings are surrounded by quotes, dates by #, and numbers
with nothing

Docmd.OpenForm "FRMAPPOINTMENTS",,,"ID = " & Me.ID

[keri]

Thanks for the reply. However I recieve an error;

Run time error 3075

Syntax error (missing operator) in expression "APPID =" & Me.APPID

Sorry for being fairly useless.

[keri]

I have had another thought on this - the form I am trying to open has
a subform (which is the only bit I need to view), however the subform
entries are based on a selection from the main form hence the reason I
cannot view the appointment in the subform.

So, I have created another appointments form based on the appointments
table. So I have changed my open form command line thingy to;

DoCmd.OpenForm "APPOINTMENTS", , , "APPID = " & Me.APPID

I now get;

Run time error 2501

The openform action was cancelled

[keri]

I now have this;

DoCmd.OpenForm "appointments", , , "appid = 'me.appid'"

which opens the form, but the record I wish to dsplay (with the
matching APPID as the APPID of the selected record in the query) is
not displayed - the form is blank although shows filtered next to the
record selector at the bottom of the screen.

Could this be because I cannot refer to a selected APPID in the query
as me.APPID?

[salad]

keri wrote:

I have had another thought on this - the form I am trying to open has
a subform (which is the only bit I need to view), however the subform
entries are based on a selection from the main form hence the reason I
cannot view the appointment in the subform.

So, I have created another appointments form based on the appointments
table. So I have changed my open form command line thingy to;

DoCmd.OpenForm "APPOINTMENTS", , , "APPID = " & Me.APPID

I now get;

Run time error 2501

The openform action was cancelled

The form's OnOpen event has a Cancel property.
Private Sub Form_Open(Cancel As Integer)

If you set Cancel = True in the OnOpen event, it will return control
back to the calling form and give you a 2501 error.

I am unsure why you need another subform. Unless the subform makes
references to the main form in its code or recordsource, you should be
able to call the subform by itself.

Can you open the old subform by itself (from the database window)? Can
you open the new form by itself?

If so, ensure you are attempting to filter on the correct fields.

If worse gets to worse, open up the form and on the line to OpenForm,
press your mouse on the left window column of the code (a red circle
will be displayed) and step thru the code process.

[keri]

I am now here and very frustrated!

Private Sub Command13_Click()
Dim thisid
Set thisid = Me.APPID
Debug.Print thisid

DoCmd.OpenForm "appointments", , , "appid = 'thisid'"

End Sub

This opens the form but does not show the correct record (once again
the record is blank but the form says filtered). I entered the print
line to see if it was assigning the correct value to the thisid
variable, and it is, yet this is obviously not being passed to the
openform filter.

If I use this line;

DoCmd.OpenForm "appointments", , , "appid = '13'"

Then the form opens with the correct appid record showing.

So why will it print thisid variable as 13 but not use this as the
filter value for the openform command?

Grrrrr!!!!

[Wayne Gillespie]

On 24 Mar 2007 07:57:41 -0700, "keri" <ke********@hotmail.comwrote:

>I am now here and very frustrated!

Private Sub Command13_Click()
Dim thisid
Set thisid = Me.APPID
Debug.Print thisid

DoCmd.OpenForm "appointments", , , "appid = 'thisid'"

End Sub

This opens the form but does not show the correct record (once again
the record is blank but the form says filtered). I entered the print
line to see if it was assigning the correct value to the thisid
variable, and it is, yet this is obviously not being passed to the
openform filter.

If I use this line;

DoCmd.OpenForm "appointments", , , "appid = '13'"

Then the form opens with the correct appid record showing.

So why will it print thisid variable as 13 but not use this as the
filter value for the openform command?

Grrrrr!!!!

It appears appid is a text value therefore it needs to be surrounded on quotes
in your criteria.

DoCmd.OpenForm "appointments", , , "appid = " & Chr(34) & me.appid & Chr(34)

Wayne Gillespie
Gosford NSW Australia

[keri]

I now get a compile error "Can't find project or library" and on debug
the Chr is highlighted!

DoCmd.OpenForm "appointments", , , "appid = " & Chr(34) & me.appid & Chr(34)

It seems i'm in a rut here!

[salad]

keri wrote:

I now get a compile error "Can't find project or library" and on debug
the Chr is highlighted!

>>DoCmd.OpenForm "appointments", , , "appid = " & Chr(34) & me.appid & Chr(34)

It seems i'm in a rut here!

I think you are having a case of bad kharma. Or perhaps your computer
is out to get you for banging the keyboard.

Open up a code module. Then from the menu click tools/references. In
my case I have Visual Basic For Applications, Microsoft Access Library
(ver#), and Microsoft DAO in that order.

CHR() is an Access function to return a character string of the Ascii value.

Wayne's example explicity surrounds your value in quotes.

In prior examples you were surrounding the whole string in quotes...
"appid = 'thisid'"
This will never work unless you want to compare appid to the value "this
id". You really want to compare it to the variable's value.

[keri]

Thanks everybody,

I think it was the computer (once I had been given correct code I knew
I couldn't go on blaming myself!) . I now have the code supplied to me
working just perfectly. I had to restart the PC for some reason - but
then I knew it was sick in the first place, and it loves to make my
life difficult!
Private Sub Command13_Click()
Dim thisid
Dim frm As Form

Set thisid = Me.APPID
Debug.Print thisid

DoCmd.OpenForm "appointments", , , "appid = " & Chr(34) & Me.APPID &
Chr(34)
DoCmd.Close acForm, "appointments query", acSaveYes
End Sub

[salad]

keri wrote:

Thanks everybody,

I think it was the computer (once I had been given correct code I knew
I couldn't go on blaming myself!) . I now have the code supplied to me
working just perfectly. I had to restart the PC for some reason - but
then I knew it was sick in the first place, and it loves to make my
life difficult!
Private Sub Command13_Click()
Dim thisid
Dim frm As Form

Set thisid = Me.APPID
Debug.Print thisid

DoCmd.OpenForm "appointments", , , "appid = " & Chr(34) & Me.APPID &
Chr(34)
DoCmd.Close acForm, "appointments query", acSaveYes
End Sub

That's good.

One comment. If your application is a small and minor one, you can
ignore my comment. Heck, you can ignore it all together regardless.

When you create a control, why use the default name supplied by the
wizard? Ex: Command13. I think it's easy to give it the name
"CommandOpenAppointments"...simply open the property sheet. When
looking at your code a verbose CommandOpenAppointments provides far more
detail about its purpose than Command13. Same with any control
receiving input or mouse presses. About the only thing I leave alone is
the Label or Line controls since I rarely have need to specify
them...unless I need to change their caption.

[keri]

After a long day and night of this you have almost lost me completely.
But right now I want to marry you as you know what you're talking
about and I don't have a clue!

I wish I had the time and money to commit myself to learning this
stuff inside out and back to front!

Basically this comment is to say thanks to all of the people that did,
and who help people like me who are fairly hopeless!

[keri]

Ps. Why Salad? Is that your actual name?

[salad]

keri wrote:

Ps. Why Salad? Is that your actual name?

No. :-).

I used to post with my real name. I also post in political newsgroups.
I must have made someone mad at one point in the political group due
to an attack I had on my account. I also thought I was getting spam
from people that harvest email accounts from newsgroups.

Salad is a non-descript name and a salad is healthy and good. In
another venue I've adopted the name CeasarSalad.

Remember to eat your vegetables.

Bye