Hi and thanks for a greate forum..Openform from a form with

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I have had this problem for some time now and cant figure it out
really..sorry to bother this forum with my repeating issue...
Form A and Form B are based on two tables/queries with one to many
relation...i want to open FormB from the related record in Form A...both
forms contains the same Unique key and one "to many" underercord also this
one with a unique key...what code..should i open form with condition..and in
that case...what is the code?..thanks a lot for you patiance..
 
Peter said:
I have had this problem for some time now and cant figure it out
really..sorry to bother this forum with my repeating issue...
Form A and Form B are based on two tables/queries with one to many
relation...i want to open FormB from the related record in Form A...both
forms contains the same Unique key and one "to many" underercord also this
one with a unique key...what code..should i open form with condition..and
in
that case...what is the code?..thanks a lot for you patiance..
Click to expand...


Code to open FormB from a command button on FormA, to display the record
with the matching field "IDField" (where IDField is a numeric field, not
text):

DoCmd.OpenForm "FormB", _
WhereCondition:="IDField = " & Me.IDField

That assumes that the field name is the same on both forms.

If IDField is a text field, the value that is concatenated into the
WhereCondition needs to be enclosed in quotes (single- or double-quotes).
If the value will never contain the single-quote character ('), you can do
it like this:

DoCmd.OpenForm "FormB", _
WhereCondition:="IDField = '" & Me.IDField & "'"

If IDField might contain the single-quote character, but will never contain
the double-quote character ("), you can do it like this:

DoCmd.OpenForm "FormB", _
WhereCondition:="IDField = " & Chr(34) & Me.IDField & Chr(34)

Note: Chr(34) is one way to specify the double-quote character. You could
also use """" -- that is, double up the quotes inside the quoted string.


If IDField might contain either single- or double-quotes, it's more
complicated. Here's one way to do it:

Const Q As String = """"
Const QQ As String = Q & Q

DoCmd.OpenForm "FormB", _
WhereCondition:="IDField = " & _
Q & Replace(Me.IDField, Q, QQ) & Q
 
I have had this problem for some time now and cant figure it out
really..sorry to bother this forum with my repeating issue...
Form A and Form B are based on two tables/queries with one to many
relation...i want to open FormB from the related record in Form A...both
forms contains the same Unique key and one "to many" underercord also this
one with a unique key...what code..should i open form with condition..and in
that case...what is the code?..thanks a lot for you patiance..
Click to expand...

I see Dirk has given you the answer to the question you posed, but... have you
considered the much simpler approach of making FormB a Subform of FormA? If
you use the unique key as the Master Link Field and the (nonunique) foreign
key as the Child Link Field you'll get this synchronization with no code at
all. If you don't have enough screen space for both the mainform controls and
a subform, you can use a Tab Control - put mainform controls on one tab page
and the subform on a second page.
 
Hi again Dirk, i tried this but i get syntax error
DoCmd.OpenForm("Single
View",[ViewAsAcFormView=acNormal],,[WhereCondition:"Case ID ="&Me.Case ID])

I can get crazy for less :-)
 
Peter said:
Hi again Dirk, i tried this but i get syntax error
DoCmd.OpenForm("Single
View",[ViewAsAcFormView=acNormal],,[WhereCondition:"Case ID ="&Me.Case
ID])
Click to expand...

That's not much like what I wrote. Try it like this:

DoCmd.OpenForm "Single View", _
WhereCondition:="[Case ID] = " & Me![Case ID]

Some of your problems come from having spaces in your form and field names,
which is legal but a bad idea because they require special handling.
 
Back
Top