Open a 2nd form using a control on the 1st form

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

Guest

Hi,

I am trying to open a 2nd form by double clicking on the ID code on the
first form (both forms are based on the same query and share the ID control.
I read the following suggestion in a 2004 post and implemented it:

"You could use another event, such as double-clicking on a field. The
following code opens a second form by clicking on the ID field, and filters
the second form by matching the ID field to the value of the current record
on the 1st form.

Private Sub ID_DblClick(Cancel As Integer)
On Error GoTo Err_ID_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PUTYOUR FORMNAME HERE"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ID_DblClick:
Exit Sub

Err_ID_DblClick:
MsgBox Err.Description
Resume Exit_ID_DblClick
End Sub"

However, when I double click on the ID control of the first form I get a
window that says: "Enter Parameter Value". It is as though it is having a
problem passing or understanting the ID code. The ID code is in this format:
"BBB-0000-00"

I would appreciate any suggstions. Thanks.

Trauton
 
It is possible that the id field is a STRING field. That "where" clause you
are using must be CORRECTLY formatted sql.

The code you should try is thus:


stLinkCriteria = "ID = '" & Me!ID & "'"
me.Refresh ' you need this to force a disk write
DoCmd.OpenForm stDocName, , , stLinkCriteria

For ease of readability, the follwing:

stLinkCriteria = "ID = ' " & Me!ID & " ' "

Note how I put in extra spaces so you can see/read the single quotes I used
here. In your code...don't use the extra spaces.
"BBB-0000-00"
Click to expand...

The above tells me that the id is a string. In sql, we have

' or " for strings
# for dates
nothing for numbers....

where city = "edmonton"
where InvoiceDate = #12,31,2005#
where ID = 123

The above sums up the rules for sql in ms-access...and you must above the
same when using the "where" clause in code to open a form....

Also, of course test opening the 2nd form on its own to ensure something
else is not prompting you....

note also have I have a me.refresh to force a disk write. IF the form in
question NEVER edits the data..then you can remove that. (on the other
hand...if edits are allowed in the first form..then you REALLY need to write
to disk before you launch another form that can edit the same record - else
they will step on top of each other).
 
Albert,

Thank you VERY MUCH that did it! The next form opened witout problem. I
especially appreciate the time you took to show me the code "with extra
spaces" otherwise, I could have missed the extra '.

The extra explanation about the "where" statements and me.refresh was very
helpful (I know I'll refer to it in the future).

Obviously I'm not a professional developer but I'd like to take some online
courses to learn more about this type of SQL coding. Any suggestions would
be appreciated.

Thanks again for your help,

Trauton
 
Glad this went well..


As for learning ms-access?...I would try any well stocked books store...and
see what they have in terms of access books....
 
Thanks Albert. The books I have purchased deal more with Access inb general
but not so much with the programming language to create queries or procedures
like the one you did. Should I be looking into VBA, SQL books?

Trauton
 
Back
Top