Entering data onto a form

[Mellstock]

I work with 13 partners for 6 core clients. Each partner has a unique
reference with each client. I have a table with the headings partner /
client / referece. 78 entries in total. When I am preparing an invoice
I need to enter these details onto a form with separate fields for
partner, client and reference.

I can enter the partner (easily from a look up list of 13) and client
(easily from a look up list of 6). Having entered the partner and
client respectively onto the forms there can now only be one reference.


Is there any way of automatically entering the reference having
previously entered the partner and client. At the moment I am faced
with entering it from a look up list of 78!

Anybody with any ideas? Thank you.

 

[Ken Sheridan]

The solution here depends on whether or it will be possible for all time to
derive the reference from the partner/client combination or not. If it is
then you don't need to (and should not) include a reference column (field)
in the invoices table; just base the form (and any invoices reports) on a
query which joins the invoices table to the table of
partners/clients/references on the partner and client columns and return the
reference in the query. You can then bind the third control on the form to
that column.

If the reference will not be permanently derived from the other two values
you can push the reference into a text box bound to the Reference field in
the invoices table by putting code in the AfterUpdate event procedure of each
of the first two combo boxes:

Dim strCriteria As String

strCriteria = "Partner = """ * Me.Partner & _
""" And Client = """ & Me.Client & """"

If Not IsNull(Me.Client + Me.Partner) Then
Me.Reference = DLookup("Reference", "YourTable", strCriteria)
End If

where YourTable is the name of the table of clients/partners/references.

Ken Sheridan
Stafford, England

 

[Duane Hookom]

This is the 3rd news group that I have read this same post from you. Please
limit your question to a single thread in a single news group.

 

[Mellstock]

Thank you Ken.

The former situation applies. I understand the principles you are
talking about but can't get the detail right at the moment.

 

[Ken Sheridan]

You need to create a query which joins the two tables on both the Partner and
Client columns. Lets assume the first table is called Invoices and the
second, the one which contains the Reference column, is called
PartnerClients. The query would be something like:

SELECT Invoices.*, PartnerClients.Reference
FROM Invoices INNER JOIN PartnerClients
ON Invoices.Parrtner = PartnerClients.Partner
AND Invoices.Client = PartnerClients.Client;

In query design view you'd create this by adding both tables then adding all
the columns from Invoices by dragging the asterisk from into the field row of
a blank column in the design grid, and by dragging Reference from the
PartnerClients table into another column in the design grid. To join the
tables drag the partner column from Invoices onto the partner column in
PartnerClients and also drag the Client column from Invoices onto Client in
PartnerClients.

If you want the records sorted in a particular order in the form add the
relevant column(s) you want to sort on to the design grid, uncheck the 'show'
checkbox(es) and select ascending or descending as appropriate. So if for
example you wanted to sort by Partner and then by Client and then by
InvoiceDate with the latest invoice first within each set of clients for that
partner you'd add the Partner, Client and InvoiceDate columns to the design
grid, uncheck the 'show' check boxes for each, and sort Partner and Client
ascending and InvoiceDate descending.

Use the query as the RecordSource of the form and add controls to the form
for each column. The control bound to the Reference column will not be
updateable, but will show the relevant reference for the Client/Partner
combination in the controls bound to those two columns. Tip: set the Locked
property of the Reference control on the form to True (Yes) and its Enabled
property to False (No). The user won't then even be able to move focus to
the control, only view it.

Ken Sheridan
Stafford, England

 

[Mellstock]

Thank you for this. I have read it twice carefully and will try it out.


Somebody else suggested Cascading Comboboxes which I have Googled. It
seems to me that a lot of database entries legitimately reduce the
possible data entries in subsequent fields. eg entering a county
reduces the number of possible towns in an address. Thanks again for
you help.

 

[Ken Sheridan]

Correlated combo boxes might be appropriate for the client and partner. How
you'd set them up would depend which you want to select first. If for
instance you select a partner first and then a client the RowSource for the
combo box bound to the Client column would be something like this:

SELECT Client
FROM PartnerClients
WHERE Partner = Form!cboPartner
ORDER BY Client;

Note that you don't need a fully qualified reference to the control; as both
controls are in the same form the Form property will do. The AfterUpdate
event procedure of the partner combo box (cboPartner in the above example)
would requery the client combo box with:

Me.cboClient.Requery

Note that correlated combo boxes only work well in single form view. In a
continuous form you can use a hybrid control made up of a text box
superimposed on a combo box to look like a single control, though performance
can be affected to some degree. As it happens I've posted a demo of various
approaches to this type of situation, using geographical entities similar to
your counties/towns example, at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The Reference would still be pulled in from the PartnerClients table via the
query, however, so would just be a text box. There is also an alternative to
including the PartnerClients table in a query; you could base the form on the
Invoices table (or more likely a sorted query based on the table) and use an
unbound computed text box on the form with a ControlSource:

=DLookup("Reference", "PartnerClients", "Partner = """ & [cboPartner] & """
And Client = """ & [cboClient] & """")

Whatever approach you use one thing you should do is create a relationship
between the PartnerClients and Invoices tables on the Partner and Client
columns. Referential integrity should be enforced in the Edit Relationship
dialogue. This ensures that a row cannot be inserted into the Invoices table
for a partner/client combination which does not exist in the PartnerClients
table. You'll first need to make those two columns the composite primary key
of PartnerClients, or at least create a unique index on the two columns. You
should also enforce Cascade Updates, so that if for any reason the name of a
partner or client is changed in PartnerClients the update will cascade
through to Invoices. You would probably not want to enforce Cascade Deletes,
however.

Ken Sheridan
Stafford, England

 

[Mellstock]

This is where I have got to. I have a table for test purposes named
"PCN" with fields P (Partner) C (Client) and N (Number).

I then produced a form to enter P, C and N. I then wrote the following
code onupdate of the C field

N.Value = DLookup("N","PCN","P.Value = P AND C.Value = C")
(Find N from PCN given that P and C have been entered)

This seems to work well until you make a mistake entering P. When P is
changed the previously filled in N does not change.

I went on to put the same code onupdate of P as well (I tried onchange
first ,no joy) and N does now change appropriately.

Interestingly if I go to a blank form and enter P nothing happens in N
(thankfully) until I enter C as well. I had expected an error message
or to have to put in something to cope with a null C field.

 

[Mellstock]

Thank you Ken I am making progress and wrote some code similar to your
DLookup suggestion. I take the point about the referential integrity /
cascade update issues. Thank you.

Correlated combo boxes might be appropriate for the client and partner. How
you'd set them up would depend which you want to select first. If for
instance you select a partner first and then a client the RowSource for the
combo box bound to the Client column would be something like this:

SELECT Client
FROM PartnerClients
WHERE Partner = Form!cboPartner
ORDER BY Client;

Note that you don't need a fully qualified reference to the control; as both
controls are in the same form the Form property will do. The AfterUpdate
event procedure of the partner combo box (cboPartner in the above example)
would requery the client combo box with:

Me.cboClient.Requery

Note that correlated combo boxes only work well in single form view. In a
continuous form you can use a hybrid control made up of a text box
superimposed on a combo box to look like a single control, though performance
can be affected to some degree. As it happens I've posted a demo of various
approaches to this type of situation, using geographical entities similar to
your counties/towns example, at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The Reference would still be pulled in from the PartnerClients table via the
query, however, so would just be a text box. There is also an alternative to
including the PartnerClients table in a query; you could base the form on the
Invoices table (or more likely a sorted query based on the table) and use an
unbound computed text box on the form with a ControlSource:

=DLookup("Reference", "PartnerClients", "Partner = """ & [cboPartner] & """
And Client = """ & [cboClient] & """")

Whatever approach you use one thing you should do is create a relationship
between the PartnerClients and Invoices tables on the Partner and Client
columns. Referential integrity should be enforced in the Edit Relationship
dialogue. This ensures that a row cannot be inserted into the Invoices table
for a partner/client combination which does not exist in the PartnerClients
table. You'll first need to make those two columns the composite primary key
of PartnerClients, or at least create a unique index on the two columns. You
should also enforce Cascade Updates, so that if for any reason the name of a
partner or client is changed in PartnerClients the update will cascade
through to Invoices. You would probably not want to enforce Cascade Deletes,
however.

Ken Sheridan
Stafford, England

Thank you for this. I have read it twice carefully and will try it out.


Somebody else suggested Cascading Comboboxes which I have Googled. It
seems to me that a lot of database entries legitimately reduce the
possible data entries in subsequent fields. eg entering a county
reduces the number of possible towns in an address. Thanks again for
you help.

 

[Ken Sheridan]

The criteria of the Dlookup function should be a string expression into which
the values of the P and C controls on the form are concatenated, not a
literal string. Also you don't need to specify the Value property as it is
the default property of a control. As P and C are of text data type the
values need to be delimited by quotes, for which you use an adjacent pair of
quotes characters within the expression (the adjacent pair of quotes within
the outer pair of delimiting quotes characters is interpreted as a literal
quotes character). Finally the N control should be a computed control, so
you don't 'push;' a value into it in code, but 'pull' the value in by setting
its ControlSource property to the expression:

=DLookup("N","PCN","P = """ & [P] & """ And C = """ & [C] & """")

Ken Sheridan
Stafford, England

 

[Mellstock]

Thank you Ken for your help and interest. I read your post when you
sent it. I do not understand the difference between "concatenated" and
"literal sring". What is the difference any why is it important.

Kind Regards

The criteria of the Dlookup function should be a string expression into which
the values of the P and C controls on the form are concatenated, not a
literal string. Also you don't need to specify the Value property as it is
the default property of a control. As P and C are of text data type the
values need to be delimited by quotes, for which you use an adjacent pair of
quotes characters within the expression (the adjacent pair of quotes within
the outer pair of delimiting quotes characters is interpreted as a literal
quotes character). Finally the N control should be a computed control, so
you don't 'push;' a value into it in code, but 'pull' the value in by setting
its ControlSource property to the expression:

=DLookup("N","PCN","P = """ & [P] & """ And C = """ & [C] & """")

Ken Sheridan
Stafford, England

This is where I have got to. I have a table for test purposes named
"PCN" with fields P (Partner) C (Client) and N (Number).

I then produced a form to enter P, C and N. I then wrote the following
code onupdate of the C field

N.Value = DLookup("N","PCN","P.Value = P AND C.Value = C")
(Find N from PCN given that P and C have been entered)

This seems to work well until you make a mistake entering P. When P is
changed the previously filled in N does not change.

I went on to put the same code onupdate of P as well (I tried onchange
first ,no joy) and N does now change appropriately.

Interestingly if I go to a blank form and enter P nothing happens in N
(thankfully) until I enter C as well. I had expected an error message
or to have to put in something to cope with a null C field.

 

[Ken Sheridan]

A literal string is a string expression which is the actual value and is
delimited by quotes characters, .e.g. "Ken Sheridan" is a literal string.
Concatenation is the building of a string expression by joining the
individual parts together. The ampersand is the usual concatenation
operator. A string expression could be concatenated with a series of literal
strings, e.g. "Ken " & "Sheridan", but will more usually be at least partly
from variables e.g. if the variable strLastName had been assigned a value
"Sheridan then "Ken" & strLastName would evaluate to my name.

In your case you had used:

DLookup("N","PCN","P.Value = P AND C.Value = C").

In this P and C (the second ones of each, that is) are variables, in this
case field names, which will hold text values. So you need to concatenate
the values in those fields into the string expression for the DLookup
function's criteria, whereas you had included them as part of the literal
sting. But another factor comes into play here, which is that values of text
data type must be wrapped in quotes, so you need to include quotes around the
values of P and C in the expression. As the quotes character is the
delimiter of a literal string, however, you can't simply put a quotes
character between the two quotes characters which already delimit the literal
string. What you can do, however, is put two adjacent quotes characters,
which are then interpreted as a single quotes character within the string,
like so:

DLookup("N","PCN","P = """ & [P] & """ And C = """ & [C] & """")

The square brackets are just Access's way of indicating that P and C are the
names of objects, and would be inserted automatically in the ControlSource if
you didn't put them in yourself. If the value of P was "Foo" and the value
of C was "Bar" say, the criteria expression above would evaluate to:

P = "Foo" And C = "Bar"

Ken Sheridan,
Stafford, England

Thank you Ken for your help and interest. I read your post when you
sent it. I do not understand the difference between "concatenated" and
"literal sring". What is the difference any why is it important.

Kind Regards

The criteria of the Dlookup function should be a string expression into which
the values of the P and C controls on the form are concatenated, not a
literal string. Also you don't need to specify the Value property as it is
the default property of a control. As P and C are of text data type the
values need to be delimited by quotes, for which you use an adjacent pair of
quotes characters within the expression (the adjacent pair of quotes within
the outer pair of delimiting quotes characters is interpreted as a literal
quotes character). Finally the N control should be a computed control, so
you don't 'push;' a value into it in code, but 'pull' the value in by setting
its ControlSource property to the expression:

=DLookup("N","PCN","P = """ & [P] & """ And C = """ & [C] & """")

Ken Sheridan
Stafford, England

This is where I have got to. I have a table for test purposes named
"PCN" with fields P (Partner) C (Client) and N (Number).

I then produced a form to enter P, C and N. I then wrote the following
code onupdate of the C field

N.Value = DLookup("N","PCN","P.Value = P AND C.Value = C")
(Find N from PCN given that P and C have been entered)

This seems to work well until you make a mistake entering P. When P is
changed the previously filled in N does not change.

I went on to put the same code onupdate of P as well (I tried onchange
first ,no joy) and N does now change appropriately.

Interestingly if I go to a blank form and enter P nothing happens in N
(thankfully) until I enter C as well. I had expected an error message
or to have to put in something to cope with a null C field.







Mellstock wrote:
I work with 13 partners for 6 core clients. Each partner has a unique
reference with each client. I have a table with the headings partner /
client / referece. 78 entries in total. When I am preparing an invoice
I need to enter these details onto a form with separate fields for
partner, client and reference.

I can enter the partner (easily from a look up list of 13) and client
(easily from a look up list of 6). Having entered the partner and
client respectively onto the forms there can now only be one reference.


Is there any way of automatically entering the reference having
previously entered the partner and client. At the moment I am faced
with entering it from a look up list of 78!

Anybody with any ideas? Thank you.