Transferring parameters

[Mishanya]

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Johnathon Anderson]

Hi

This is exactly what the primary field is for. To identify the record.

What if you had 2 John Smiths. Your application would not know which record
to open the new form to.

You can place the ID field on the form but set it to Visible=No. This way
you can reference it to open the new form but your users would never see it.

 

[Ken Sheridan]

1. Assuming the form with the ClientName control includes the ClientID field
in its underlying recordset, the code is much the same as that used for
opening frmCLientPreferences, with a few amendments. You don't have to
include a control for the autonumber ClientID column on the form; its
existence in the underlying recordset is enough for the code to be able to
reference it (this isn't true in reports BTW, only in forms)

Const conFORM = "frmClientDetails"
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
OpenArgs:=Me.ClientID

By way of explanation:

1.1 As the form name doesn't change assign it to a constant rather than a
variable; I know the wizard uses a variable, but even wizard's aren't perfect.

1.2 When calling the OpenForm method the arguments are specifically named
rather than putting a series of commas with spaces. This makes the code more
readable.

1.3 The line calling the OpenForm method is split over several lines using
the underscore continuation character; again this makes for greater
readability.

1.4 This assumes that the control bound to the ClientID field in
frmClientDetails is also named ClientID. This is because in 1.5 below its
the control's DefaultValue property being set, not the underlying field's.

1.5 In addition to the WhereCondition argument (which filters the form
being opened) the ClientID is passed as the OpenArgs argument, which leads us
on to the next step:

In frmClientDetails Open event procedure put the following code to set the
DefaultValue property of the ClientID control. This will automatically
insert the correct value if you add a new Client Details record for the
client in question:

If Not IsNull(Me.OpenArgs) Then
Me.ClientID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the value is wrapped in quotes characters even though ClientID is
a number data type. This is because the DefaultValue property is always a
string expression regardless of the data type of the field in question.
Often omitting the quotes doesn't matter, but sometimes they are essential
(withdate/time values particularly) so its prudent to always include them.

2. In frmCLientPreferences use a combo box set up as follows:

Name: ClientID

RowSource: SELECT ClientID, ClientName FROM tblClients ORDER BY
ClientName;

ControlSource: ClientID
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If the client names are personal names rather than company names it would be
better to split the ClientName column into FirstName and LastName columns
(its easy to concatenate the values from two columns, but less easy to parse
out two values from a single value) in which case the combo box's RowSource
property would be as follows:

RowSource: SELECT ClientID, (FirstName + " " ) & LastName FROM
tblClients ORDER BY LastName, FirstName;

You might want to consider amending your code which opens
frmCLientPreferences so its similar to that above for opening
frmClientDetails, and then putting code in frmClientPreferences' Open event
procedure to set its ClientID control's DefaultValue property so that the
value would automatically be inserted if you are entering a new record in
frmClientPreferences.

Finally, one last point for you to consider. If you want to force the user
to close the form being opened before returning to the calling form you can
do by opening it in dialogue mode, which is done by means of the OpenForm
method's WindowMode argument, using the acDialog constant e.g.

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Mishanya]

Thank You for answer.
May You, if so, specify:

I) In the 1st case, if I put invisible field ClientID somewhere in the 1st
form, can I still use another field (ClientName) for opening frmClientDetails
by double-clicking, coding its DblClick Event as:

DoCmd.OpenForm "frmClientDetails",,, "ClientID =" & Me.[ClientID] ?

2) As for the 2nd case, how do I transfer ClientName value to the form,
based on table which have CientID only?

Hi

This is exactly what the primary field is for. To identify the record.

What if you had 2 John Smiths. Your application would not know which record
to open the new form to.

You can place the ID field on the form but set it to Visible=No. This way
you can reference it to open the new form but your users would never see it.

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Ken Sheridan]

Correction to my last post. I missed a comma and constitution character in:

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog, _
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

 

[Mishanya]

Ken, Hi!
Thank You for Your profound explanation.

1) As for the first case, the form with the ClientName control is based on
query, wich uses only the ClientName field from tblClientsDetails. Do I have
to add ClientID-field to the query (even if it is not used in the form)? If
so, do I have to pull it from tblClientDetails (Primary Key), or it may be
pulled from the other table used in the query (Foreign Key)?
Meantime I've tried the both ways and put Your code under DblClick Event of
ClientName - the compiler does not like it ("Method or Data member not
found").

2) As for the 2nd case, the frmClientPreferences is View-Only (the data is
entered in another though similar-structure form, where the ClientID field is
Combobox built as You described). So, it is due to my wish to prevent the
user from playing with Combo in the View-only form, I turned the field to
txtBox. So now the form opens on the right record, only instead of the name I
get AutoNumber. My wish is, though, to see the ClientName. So I wonder, how
can I "trick" it - to deliver ClientName from tblClientNames, even that it is
not in the tblClientPreferences (on which the form is based), without using
Combo. I could put ClientName as a field to the tblClientPreferences as
well, but this would be redundant DB-rules-wise.

Anyway, I'm rookie in Access, so maybe my logic is still slightly "pervert.
And certainly that's the reason for not succeeding to implement Your
instruction in the 1st case.

Hope, my English is OK (my Russianis much better )

1. Assuming the form with the ClientName control includes the ClientID field
in its underlying recordset, the code is much the same as that used for
opening frmCLientPreferences, with a few amendments. You don't have to
include a control for the autonumber ClientID column on the form; its
existence in the underlying recordset is enough for the code to be able to
reference it (this isn't true in reports BTW, only in forms)

Const conFORM = "frmClientDetails"
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
OpenArgs:=Me.ClientID

By way of explanation:

1.1 As the form name doesn't change assign it to a constant rather than a
variable; I know the wizard uses a variable, but even wizard's aren't perfect.

1.2 When calling the OpenForm method the arguments are specifically named
rather than putting a series of commas with spaces. This makes the code more
readable.

1.3 The line calling the OpenForm method is split over several lines using
the underscore continuation character; again this makes for greater
readability.

1.4 This assumes that the control bound to the ClientID field in
frmClientDetails is also named ClientID. This is because in 1.5 below its
the control's DefaultValue property being set, not the underlying field's.

1.5 In addition to the WhereCondition argument (which filters the form
being opened) the ClientID is passed as the OpenArgs argument, which leads us
on to the next step:

In frmClientDetails Open event procedure put the following code to set the
DefaultValue property of the ClientID control. This will automatically
insert the correct value if you add a new Client Details record for the
client in question:

If Not IsNull(Me.OpenArgs) Then
Me.ClientID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the value is wrapped in quotes characters even though ClientID is
a number data type. This is because the DefaultValue property is always a
string expression regardless of the data type of the field in question.
Often omitting the quotes doesn't matter, but sometimes they are essential
(withdate/time values particularly) so its prudent to always include them.

2. In frmCLientPreferences use a combo box set up as follows:

Name: ClientID

RowSource: SELECT ClientID, ClientName FROM tblClients ORDER BY
ClientName;

ControlSource: ClientID
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If the client names are personal names rather than company names it would be
better to split the ClientName column into FirstName and LastName columns
(its easy to concatenate the values from two columns, but less easy to parse
out two values from a single value) in which case the combo box's RowSource
property would be as follows:

RowSource: SELECT ClientID, (FirstName + " " ) & LastName FROM
tblClients ORDER BY LastName, FirstName;

You might want to consider amending your code which opens
frmCLientPreferences so its similar to that above for opening
frmClientDetails, and then putting code in frmClientPreferences' Open event
procedure to set its ClientID control's DefaultValue property so that the
value would automatically be inserted if you are entering a new record in
frmClientPreferences.

Finally, one last point for you to consider. If you want to force the user
to close the form being opened before returning to the calling form you can
do by opening it in dialogue mode, which is done by means of the OpenForm
method's WindowMode argument, using the acDialog constant e.g.

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Johnathon Anderson]

I) In the 1st case, if I put invisible field ClientID somewhere in the 1st

form, can I still use another field (ClientName) for opening frmClientDetails
by double-clicking, coding its DblClick Event as:

DoCmd.OpenForm "frmClientDetails",,, "ClientID =" & Me.[ClientID] ?

Yes you can (as long as there is a control on your 1st form called ClientID
an you have the same value (data) somewhere on your 2nd form

2) As for the 2nd case, how do I transfer ClientName value to the form,
based on table which have CientID only?

You don't need to. On each form you have the ClientID and the name + other
controls and information. So when you open a form based on a clientID the
name will still be in the 2nd form.


It may not look like it (after reading Ken's post - who's answer seem to get
more like a desperate plea to become an MVP each day) but it really is quite
simple. I think you may want to just try and get your database to perform
the basics that you need and want 1st and then after this works you can start
looking at all the other bits of database design.

Johnathon

Thank You for answer.
May You, if so, specify:

I) In the 1st case, if I put invisible field ClientID somewhere in the 1st
form, can I still use another field (ClientName) for opening frmClientDetails
by double-clicking, coding its DblClick Event as:

DoCmd.OpenForm "frmClientDetails",,, "ClientID =" & Me.[ClientID] ?

2) As for the 2nd case, how do I transfer ClientName value to the form,
based on table which have CientID only?

Hi

This is exactly what the primary field is for. To identify the record.

What if you had 2 John Smiths. Your application would not know which record
to open the new form to.

You can place the ID field on the form but set it to Visible=No. This way
you can reference it to open the new form but your users would never see it.

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Ken Sheridan]

Your English is excellent. I'm sorry to say that my Russian is non-existent,
though, so if there is anything which I don't make clear please do ask me to
explain it.

1. The ClientID column should be included in the underlying tables or
queries of both the form from which you are opening the frmClientDetails
form, and the frmClientDetails form itself. Its needed in the first form so
that you can reference it in the DblClick event procedure, and in the second
so that the form can be filtered on it. They don't need to be shown on the
forms however, but you do need a control named ClientID and bound to the
ClientID field on frmClientDetails so that you can set its DefaultValue
property. A combo box would be the usual type of control to use, set up as I
described so it is bound to ClientID, but shows ClientName, but you could use
a hidden text box, named ClientID and with ClientID as its ControlSource,
and an unbound visible text box, called something different e.g.,
txtClientName with the expression:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

as its ControlSource property. You would not be able to change the name,
but that's probably an advantage in this case.

2. As frmClientPreferences is read only you don't need to pass the
ClientID as the OpenArgs property, so you can open it with:

Const conFORM = "frmClientPreferences "
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria

Rather than use a combo box on the frmClientPreferences form you can, as
above, show the name in an unbound text box by looking the name up from the
clients table, so the ControlSource property would again be:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

A simpler, and more efficient way would be include tblClients (or whatever
your clients table is called) in a query used as the RecordSource of
frmClientPreferences, joined to tblClientPreferences on ClientID. Its by
joining tables in queries like this that you avoid the redundancy of
repeating the same data in two tables. You can then include a control bound
to the ClientName field.

In this form you don't need the hidden ClientID text box, however.

Ken Sheridan
Stafford, England

Ken, Hi!
Thank You for Your profound explanation.

1) As for the first case, the form with the ClientName control is based on
query, wich uses only the ClientName field from tblClientsDetails. Do I have
to add ClientID-field to the query (even if it is not used in the form)? If
so, do I have to pull it from tblClientDetails (Primary Key), or it may be
pulled from the other table used in the query (Foreign Key)?
Meantime I've tried the both ways and put Your code under DblClick Event of
ClientName - the compiler does not like it ("Method or Data member not
found").

2) As for the 2nd case, the frmClientPreferences is View-Only (the data is
entered in another though similar-structure form, where the ClientID field is
Combobox built as You described). So, it is due to my wish to prevent the
user from playing with Combo in the View-only form, I turned the field to
txtBox. So now the form opens on the right record, only instead of the name I
get AutoNumber. My wish is, though, to see the ClientName. So I wonder, how
can I "trick" it - to deliver ClientName from tblClientNames, even that it is
not in the tblClientPreferences (on which the form is based), without using
Combo. I could put ClientName as a field to the tblClientPreferences as
well, but this would be redundant DB-rules-wise.

Anyway, I'm rookie in Access, so maybe my logic is still slightly "pervert.
And certainly that's the reason for not succeeding to implement Your
instruction in the 1st case.

Hope, my English is OK (my Russianis much better )

1. Assuming the form with the ClientName control includes the ClientID field
in its underlying recordset, the code is much the same as that used for
opening frmCLientPreferences, with a few amendments. You don't have to
include a control for the autonumber ClientID column on the form; its
existence in the underlying recordset is enough for the code to be able to
reference it (this isn't true in reports BTW, only in forms)

Const conFORM = "frmClientDetails"
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
OpenArgs:=Me.ClientID

By way of explanation:

1.1 As the form name doesn't change assign it to a constant rather than a
variable; I know the wizard uses a variable, but even wizard's aren't perfect.

1.2 When calling the OpenForm method the arguments are specifically named
rather than putting a series of commas with spaces. This makes the code more
readable.

1.3 The line calling the OpenForm method is split over several lines using
the underscore continuation character; again this makes for greater
readability.

1.4 This assumes that the control bound to the ClientID field in
frmClientDetails is also named ClientID. This is because in 1.5 below its
the control's DefaultValue property being set, not the underlying field's.

1.5 In addition to the WhereCondition argument (which filters the form
being opened) the ClientID is passed as the OpenArgs argument, which leads us
on to the next step:

In frmClientDetails Open event procedure put the following code to set the
DefaultValue property of the ClientID control. This will automatically
insert the correct value if you add a new Client Details record for the
client in question:

If Not IsNull(Me.OpenArgs) Then
Me.ClientID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the value is wrapped in quotes characters even though ClientID is
a number data type. This is because the DefaultValue property is always a
string expression regardless of the data type of the field in question.
Often omitting the quotes doesn't matter, but sometimes they are essential
(withdate/time values particularly) so its prudent to always include them.

2. In frmCLientPreferences use a combo box set up as follows:

Name: ClientID

RowSource: SELECT ClientID, ClientName FROM tblClients ORDER BY
ClientName;

ControlSource: ClientID
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If the client names are personal names rather than company names it would be
better to split the ClientName column into FirstName and LastName columns
(its easy to concatenate the values from two columns, but less easy to parse
out two values from a single value) in which case the combo box's RowSource
property would be as follows:

RowSource: SELECT ClientID, (FirstName + " " ) & LastName FROM
tblClients ORDER BY LastName, FirstName;

You might want to consider amending your code which opens
frmCLientPreferences so its similar to that above for opening
frmClientDetails, and then putting code in frmClientPreferences' Open event
procedure to set its ClientID control's DefaultValue property so that the
value would automatically be inserted if you are entering a new record in
frmClientPreferences.

Finally, one last point for you to consider. If you want to force the user
to close the form being opened before returning to the calling form you can
do by opening it in dialogue mode, which is done by means of the OpenForm
method's WindowMode argument, using the acDialog constant e.g.

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Mishanya]

Thanks again for Your patience.

The DLookUp function perfectly solves the 2nd case (although for the last 2
hours I've been going banans trying to put the code in the ControlSource, but
whatever I do - pasting it exactly or playing with []s and "s, I keep on
receiving Error Msg "Operator missing, illegal sign or exessive comma or line
with no quotes" loosely translated from Russian).

As for the 1st case You wrote:
"The ClientID column should be included in the underlying tables or queries
of both the form from which you are opening the frmClientDetails form, and
the frmClientDetails form itself."

Eventually it works even if I don't include the ClientID column in the
frmClientDetails itself. Mabe some Russian-edition magic

Thanks again! I'm back to strive with the DlookUp.

Your English is excellent. I'm sorry to say that my Russian is non-existent,
though, so if there is anything which I don't make clear please do ask me to
explain it.

1. The ClientID column should be included in the underlying tables or
queries of both the form from which you are opening the frmClientDetails
form, and the frmClientDetails form itself. Its needed in the first form so
that you can reference it in the DblClick event procedure, and in the second
so that the form can be filtered on it. They don't need to be shown on the
forms however, but you do need a control named ClientID and bound to the
ClientID field on frmClientDetails so that you can set its DefaultValue
property. A combo box would be the usual type of control to use, set up as I
described so it is bound to ClientID, but shows ClientName, but you could use
a hidden text box, named ClientID and with ClientID as its ControlSource,
and an unbound visible text box, called something different e.g.,
txtClientName with the expression:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

as its ControlSource property. You would not be able to change the name,
but that's probably an advantage in this case.

2. As frmClientPreferences is read only you don't need to pass the
ClientID as the OpenArgs property, so you can open it with:

Const conFORM = "frmClientPreferences "
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria

Rather than use a combo box on the frmClientPreferences form you can, as
above, show the name in an unbound text box by looking the name up from the
clients table, so the ControlSource property would again be:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

A simpler, and more efficient way would be include tblClients (or whatever
your clients table is called) in a query used as the RecordSource of
frmClientPreferences, joined to tblClientPreferences on ClientID. Its by
joining tables in queries like this that you avoid the redundancy of
repeating the same data in two tables. You can then include a control bound
to the ClientName field.

In this form you don't need the hidden ClientID text box, however.

Ken Sheridan
Stafford, England

Ken, Hi!
Thank You for Your profound explanation.

1) As for the first case, the form with the ClientName control is based on
query, wich uses only the ClientName field from tblClientsDetails. Do I have
to add ClientID-field to the query (even if it is not used in the form)? If
so, do I have to pull it from tblClientDetails (Primary Key), or it may be
pulled from the other table used in the query (Foreign Key)?
Meantime I've tried the both ways and put Your code under DblClick Event of
ClientName - the compiler does not like it ("Method or Data member not
found").

2) As for the 2nd case, the frmClientPreferences is View-Only (the data is
entered in another though similar-structure form, where the ClientID field is
Combobox built as You described). So, it is due to my wish to prevent the
user from playing with Combo in the View-only form, I turned the field to
txtBox. So now the form opens on the right record, only instead of the name I
get AutoNumber. My wish is, though, to see the ClientName. So I wonder, how
can I "trick" it - to deliver ClientName from tblClientNames, even that it is
not in the tblClientPreferences (on which the form is based), without using
Combo. I could put ClientName as a field to the tblClientPreferences as
well, but this would be redundant DB-rules-wise.

Anyway, I'm rookie in Access, so maybe my logic is still slightly "pervert.
And certainly that's the reason for not succeeding to implement Your
instruction in the 1st case.

Hope, my English is OK (my Russianis much better )

1. Assuming the form with the ClientName control includes the ClientID field
in its underlying recordset, the code is much the same as that used for
opening frmCLientPreferences, with a few amendments. You don't have to
include a control for the autonumber ClientID column on the form; its
existence in the underlying recordset is enough for the code to be able to
reference it (this isn't true in reports BTW, only in forms)

Const conFORM = "frmClientDetails"
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
OpenArgs:=Me.ClientID

By way of explanation:

1.1 As the form name doesn't change assign it to a constant rather than a
variable; I know the wizard uses a variable, but even wizard's aren't perfect.

1.2 When calling the OpenForm method the arguments are specifically named
rather than putting a series of commas with spaces. This makes the code more
readable.

1.3 The line calling the OpenForm method is split over several lines using
the underscore continuation character; again this makes for greater
readability.

1.4 This assumes that the control bound to the ClientID field in
frmClientDetails is also named ClientID. This is because in 1.5 below its
the control's DefaultValue property being set, not the underlying field's.

1.5 In addition to the WhereCondition argument (which filters the form
being opened) the ClientID is passed as the OpenArgs argument, which leads us
on to the next step:

In frmClientDetails Open event procedure put the following code to set the
DefaultValue property of the ClientID control. This will automatically
insert the correct value if you add a new Client Details record for the
client in question:

If Not IsNull(Me.OpenArgs) Then
Me.ClientID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the value is wrapped in quotes characters even though ClientID is
a number data type. This is because the DefaultValue property is always a
string expression regardless of the data type of the field in question.
Often omitting the quotes doesn't matter, but sometimes they are essential
(withdate/time values particularly) so its prudent to always include them.

2. In frmCLientPreferences use a combo box set up as follows:

Name: ClientID

RowSource: SELECT ClientID, ClientName FROM tblClients ORDER BY
ClientName;

ControlSource: ClientID
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If the client names are personal names rather than company names it would be
better to split the ClientName column into FirstName and LastName columns
(its easy to concatenate the values from two columns, but less easy to parse
out two values from a single value) in which case the combo box's RowSource
property would be as follows:

RowSource: SELECT ClientID, (FirstName + " " ) & LastName FROM
tblClients ORDER BY LastName, FirstName;

You might want to consider amending your code which opens
frmCLientPreferences so its similar to that above for opening
frmClientDetails, and then putting code in frmClientPreferences' Open event
procedure to set its ClientID control's DefaultValue property so that the
value would automatically be inserted if you are entering a new record in
frmClientPreferences.

Finally, one last point for you to consider. If you want to force the user
to close the form being opened before returning to the calling form you can
do by opening it in dialogue mode, which is done by means of the OpenForm
method's WindowMode argument, using the acDialog constant e.g.

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

:

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[Mishanya]

Ken, I'm desperate.
When I put the line:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

in the form On Load Event, it works fine. But when I put the same line

= DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

in the txtClientName ControlSource Property, I get Error msg "Operator
missing, exessive commas or line with no quotes"

What's wrong?

Your English is excellent. I'm sorry to say that my Russian is non-existent,
though, so if there is anything which I don't make clear please do ask me to
explain it.

1. The ClientID column should be included in the underlying tables or
queries of both the form from which you are opening the frmClientDetails
form, and the frmClientDetails form itself. Its needed in the first form so
that you can reference it in the DblClick event procedure, and in the second
so that the form can be filtered on it. They don't need to be shown on the
forms however, but you do need a control named ClientID and bound to the
ClientID field on frmClientDetails so that you can set its DefaultValue
property. A combo box would be the usual type of control to use, set up as I
described so it is bound to ClientID, but shows ClientName, but you could use
a hidden text box, named ClientID and with ClientID as its ControlSource,
and an unbound visible text box, called something different e.g.,
txtClientName with the expression:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

as its ControlSource property. You would not be able to change the name,
but that's probably an advantage in this case.

2. As frmClientPreferences is read only you don't need to pass the
ClientID as the OpenArgs property, so you can open it with:

Const conFORM = "frmClientPreferences "
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria

Rather than use a combo box on the frmClientPreferences form you can, as
above, show the name in an unbound text box by looking the name up from the
clients table, so the ControlSource property would again be:

=DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])

A simpler, and more efficient way would be include tblClients (or whatever
your clients table is called) in a query used as the RecordSource of
frmClientPreferences, joined to tblClientPreferences on ClientID. Its by
joining tables in queries like this that you avoid the redundancy of
repeating the same data in two tables. You can then include a control bound
to the ClientName field.

In this form you don't need the hidden ClientID text box, however.

Ken Sheridan
Stafford, England

Ken, Hi!
Thank You for Your profound explanation.

1) As for the first case, the form with the ClientName control is based on
query, wich uses only the ClientName field from tblClientsDetails. Do I have
to add ClientID-field to the query (even if it is not used in the form)? If
so, do I have to pull it from tblClientDetails (Primary Key), or it may be
pulled from the other table used in the query (Foreign Key)?
Meantime I've tried the both ways and put Your code under DblClick Event of
ClientName - the compiler does not like it ("Method or Data member not
found").

2) As for the 2nd case, the frmClientPreferences is View-Only (the data is
entered in another though similar-structure form, where the ClientID field is
Combobox built as You described). So, it is due to my wish to prevent the
user from playing with Combo in the View-only form, I turned the field to
txtBox. So now the form opens on the right record, only instead of the name I
get AutoNumber. My wish is, though, to see the ClientName. So I wonder, how
can I "trick" it - to deliver ClientName from tblClientNames, even that it is
not in the tblClientPreferences (on which the form is based), without using
Combo. I could put ClientName as a field to the tblClientPreferences as
well, but this would be redundant DB-rules-wise.

Anyway, I'm rookie in Access, so maybe my logic is still slightly "pervert.
And certainly that's the reason for not succeeding to implement Your
instruction in the 1st case.

Hope, my English is OK (my Russianis much better )

1. Assuming the form with the ClientName control includes the ClientID field
in its underlying recordset, the code is much the same as that used for
opening frmCLientPreferences, with a few amendments. You don't have to
include a control for the autonumber ClientID column on the form; its
existence in the underlying recordset is enough for the code to be able to
reference it (this isn't true in reports BTW, only in forms)

Const conFORM = "frmClientDetails"
Dim strCriteria As String

strCriteria = "ClientID = " & Me.ClientID

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
OpenArgs:=Me.ClientID

By way of explanation:

1.1 As the form name doesn't change assign it to a constant rather than a
variable; I know the wizard uses a variable, but even wizard's aren't perfect.

1.2 When calling the OpenForm method the arguments are specifically named
rather than putting a series of commas with spaces. This makes the code more
readable.

1.3 The line calling the OpenForm method is split over several lines using
the underscore continuation character; again this makes for greater
readability.

1.4 This assumes that the control bound to the ClientID field in
frmClientDetails is also named ClientID. This is because in 1.5 below its
the control's DefaultValue property being set, not the underlying field's.

1.5 In addition to the WhereCondition argument (which filters the form
being opened) the ClientID is passed as the OpenArgs argument, which leads us
on to the next step:

In frmClientDetails Open event procedure put the following code to set the
DefaultValue property of the ClientID control. This will automatically
insert the correct value if you add a new Client Details record for the
client in question:

If Not IsNull(Me.OpenArgs) Then
Me.ClientID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the value is wrapped in quotes characters even though ClientID is
a number data type. This is because the DefaultValue property is always a
string expression regardless of the data type of the field in question.
Often omitting the quotes doesn't matter, but sometimes they are essential
(withdate/time values particularly) so its prudent to always include them.

2. In frmCLientPreferences use a combo box set up as follows:

Name: ClientID

RowSource: SELECT ClientID, ClientName FROM tblClients ORDER BY
ClientName;

ControlSource: ClientID
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If the client names are personal names rather than company names it would be
better to split the ClientName column into FirstName and LastName columns
(its easy to concatenate the values from two columns, but less easy to parse
out two values from a single value) in which case the combo box's RowSource
property would be as follows:

RowSource: SELECT ClientID, (FirstName + " " ) & LastName FROM
tblClients ORDER BY LastName, FirstName;

You might want to consider amending your code which opens
frmCLientPreferences so its similar to that above for opening
frmClientDetails, and then putting code in frmClientPreferences' Open event
procedure to set its ClientID control's DefaultValue property so that the
value would automatically be inserted if you are entering a new record in
frmClientPreferences.

Finally, one last point for you to consider. If you want to force the user
to close the form being opened before returning to the calling form you can
do by opening it in dialogue mode, which is done by means of the OpenForm
method's WindowMode argument, using the acDialog constant e.g.

DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
OpenArgs:=Me.ClientID

Ken Sheridan
Stafford, England

:

1. I have a form with the field ClientName and want to open frmClientDetails
by doublclicking it. What code do I have to put in the DblClick Event of
ClientName, so that it will open the needed recordset (from ClientName in the
1st form to the ClientID-recordset in the frmClientDetails)?

DoCmd.OpenForm "frmClientDetails",,, "??? =" & Me.[ClientName]

P.S.: It would be easy to put the ClientID field in the 1st form and wrap
the DblClick event on it, but I don't want this Autonumber to be seen in the
form.

2. I have a switchbord where I chose ClientName from cboCLientName (based on
ClientID and CLientName fields from the tblClientDetails) and click on
Button wich opens frmCLientPreferences (based on tblCLientPreferences where
ClientID is foreign key). The form opens with the recordset of prechoosen
Client. The procedure of the Button contains:

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

What should I add in order to have unbound ClientName field in the
frmCLientPreferences filled (the procedure only transfers the ClientID).

P.S.: I could build sub_frmCLientPreferences and put it under
main_frmClientName (based on the tblCLientDetails) wich would receive the
ClientID and show the ClientName, but there is only one Preferences-recordset
for each client, so maybe it can be avoided.

 

[John W. Vinson]

Ken, I'm desperate.
When I put the line:

ClientName = DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

in the form On Load Event, it works fine. But when I put the same line

= DLookup("ClientName", "tblClients", "ClientID = " & ClientID)

in the txtClientName ControlSource Property, I get Error msg "Operator
missing, exessive commas or line with no quotes"

You'll get an error like this if ClientID is NULL. If there is no client 0,
try

= DLookup("ClientName", "tblClients", "ClientID = " & NZ(ClientID, 0))

 

[Mishanya]

The thing is I don't even get to run the form - the error-msg pops when I try
to leave the ControlSource Property, so I can't even save it. As if there is
a syntax-error, not data-error.

 

[John W. Vinson]

The thing is I don't even get to run the form - the error-msg pops when I try
to leave the ControlSource Property, so I can't even save it. As if there is
a syntax-error, not data-error.

Sorry!

Try

= DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])


The brackets will inform Access that you're talking about a controlname on the
form. If the textbox bound to ClientID has another name, use that name instead
of the fieldname.

 

[Mishanya]

Tried this too - same effect - same message and I can't even save and leave
the ControlSource Property. As if Access does not let me use this function,
even before it checks whether the form has the ClientID controlname or has
not.

The thing is I don't even get to run the form - the error-msg pops when I try
to leave the ControlSource Property, so I can't even save it. As if there is
a syntax-error, not data-error.

Sorry!

Try

= DLookup("ClientName", "tblClients", "ClientID = " & [ClientID])


The brackets will inform Access that you're talking about a controlname on the
form. If the textbox bound to ClientID has another name, use that name instead
of the fieldname.