opening form depending on value and passing another along.

[Krazy Darcy]

I want to open one of two(or more) forms with their underlying queries, which
one is opened is based on what is in the current record (on the search
results form) "pagesize" field, but use the value from field "record number".

this is so I can have differant details screens depending on the the
pagesize field when I call up details of a selected result on a search
results form.The search results form currently uses a macro to open the one
details form but what I actually need is to open certail details screen ie:

if pagesize = digital then open digitaldetails
else if pagesize = costume then open costumedetails
else open paperdetails

I tried to poke in vba (I can't program) and could open a form but couldn't
input data into the underlying query when the form opened.

the reason for this is an existing database application where artworks have
only a thumbnail image embedded in the catalog and text fields referring to
where the original is but i need to have several images for where a creative
piece is a costume (amidst costume specific stuff)

 

[Klatuu]

I got lost in some of your explanation, but if I understand, all you need to
do is choose the form to open based on the value in pagesize.

I don't know what digital or costume are, but based on you post, I will
assume they are values. Here is an example of how you can accomplish this:

Dim strRpt As String

Select Case pagesize
Case "digital"
strRpt = "digitaldetails"
Case "costume"
strRpt = "costumedetails"
Case Else
strRpt = "paperdetails"
End Select

Docmd.OpenReport strRpt

 

[Krazy Darcy]

Does this code go into the code for the search form that I run it from?

Pagesize is a field in the search form that refers to the underlying
matching field in the main artworks table.
At the same time as choosing the details form, I need to open the current
record (when you double click on the "record number") into the details form
to view all of it as only certain fields are shown on the search form. I have
been using a macro to do this bit but that was when using a single common
details form.

 

[Klatuu]

You run it from the search form. Probably in the double click code of the
record number, but I don't know your form design.

As to opening it on the current record, you can use the Where argument of
the OpenForm method to tell the form which record to open on.

 

[Krazy Darcy]

The code you provided has been tested without
, , "recordnumber = '" & Me.recordnumber & ""
and opens the appropriate form - Great

But...
This is what i currently have
-------code starts-----------------
Private Sub recordnumber_DblClick(Cancel As Integer)
Dim strRpt As String

Select Case pagesize
Case "digital"
strRpt = "digitaldetails"
Case "Fabric/cloth"
strRpt = "costumedetails"
Case Else
strRpt = "paperdetails"
End Select

DoCmd.OpenForm strRpt, , , "recordnumber = '" & Me.recordnumber & ""

End Sub

----------code ends------------------

When I "double click" on the recordnumber field for a record I get:

Run-Time error '3075':

Syntax error in string in query expression 'recordnumber = '585'.

This tells me that it is reading the value of recordnumber for the current
record which was the record with recordnumber 585,
but i need the value to be passed on to the form being opened so it filters
out all but the desired record.

 

[Klatuu]

As written, it is expecting a field in the record source of the form being
opened named recordnumber and it is expecting it is a text field because you
have it in quotes.

Recordnumber is an unusual name for a field. There are no record numbers in
Access. The record number you see in the navigation control, for example, is
only a relative transient value based on the current order of the records.

 

[John W. Vinson]

This is what i currently have
-------code starts-----------------
Private Sub recordnumber_DblClick(Cancel As Integer)
Dim strRpt As String

Select Case pagesize
Case "digital"
strRpt = "digitaldetails"
Case "Fabric/cloth"
strRpt = "costumedetails"
Case Else
strRpt = "paperdetails"
End Select

DoCmd.OpenForm strRpt, , , "recordnumber = '" & Me.recordnumber & ""

End Sub

----------code ends------------------

When I "double click" on the recordnumber field for a record I get:

Run-Time error '3075':

Syntax error in string in query expression 'recordnumber = '585'.

If recordnumber is a Number (or Autonumber) datatype, you should omit the
quotemarks:

DoCmd.OpenForm strRpt, , , "recordnumber = " & Me.recordnumber

The quotes are required for Text fields but forbidden for Number fields.

 

[Krazy Darcy]

THANKS GUYS

RecordNumber is a autonumber field that asigns the record number when the
record is created with the dataentry form. This was due to the fact none of
the other data is truly unique such as 2 images may have the same title, or
two drawings created on the same date. The system is a catalog of my
drawings, digital art, costuming - my creative output. Your help means that
I can now put photos of costumes into it and have that shown in the costumes
details form.

If I could mark both of you as correct answer I would, as Klatuu you got the
form selection working while John fixed the passing of the value.

THANKS GUYS

 

[John W. Vinson]

If I could mark both of you as correct answer I would, as Klatuu you got the
form selection working while John fixed the passing of the value.

Not to worry. I don't know why that button is even there; I never use the web
interface and neither I nor my MVP lead count "correct" or "answered the
question" clicks. I don't know who does, if anyone does at all.