Using an Unbound Text Box

  • Thread starter Thread starter Joy M
  • Start date Start date
J

Joy M

Hi -

Here is a quick run down of the situation:

tblClient
cliAccountNo PK
cliFirstName
cliLastName
cliAddress1
cliAddress2

tblInvoice
invInvoiceNo PK
invAccountNo FK
other Invoice fields

My form, frmInvoice, uses tblInvoice for RecordSource. It has a combo box
cboSelectClient which displays the clients first and last name, and stores
cliAccountNo in invAccountNo.

The question is - now that I have a value for cliAccountNo when I am using
frmInvoice, can I do some SQL so that I can display cliAddress1, cliAddress2
in unbound text boxes on frmInvoice?

Does this mean I should use query which contains tblInvoice and tblClient as
the RecordSource? If I do that, I want to just write a new Invoice record
when all the fields are full.

Is this possible? I imagine I would set the value of txtAddress1 in the
subprogram cboSelectClient_AfterUpdate. Do I use the Value or the Text
property of the unbound text box? Do I use SQL here?

If you know of any articles I can read, that might be helpful. Thanks for
your insight.

Joy
 
Method One (The easiest)
Base cboSelectClient on SELECT cliAccountNo, cliLastName, cliFirstName,
cliAddress1, cliAddress2
Set the column widths of columns to 1;1;1;0;0 so the address does not
display
Create two unbound text boxes on your form txtAddress1, txtAddress2
Set the ControlSource property of txtAddress1 to cboSelectClient.Column(3)
Set the ControlSource property of txtAddress2 to cboSelectClient.Column(4)

Method Two:
Base cboSelectClient on SELECT cliAccountNo, cliLastName, cliFirstName,
cliAddress1, cliAddress2
Set the column widths of columns to 1;1;1;0;0 so the address does not
display

In the AfterUpdate event of cboSelectClient:
txtAddress1 = cboSelectClient.Column(3)
txtAddress2 = cboSelectClient.Column(4)

Method Three:
Base cboSelectClient on SELECT cliAccountNo, cliLastName
Set the column widths of columns 4 and 5 to 0 so they do not display
In the AfterUpdate event of cboSelectClient create a recordset and use that
recordset to populate the controls:

Dim rst as dao.recordset
Dim db as dao.database
set db = currentdb()
SET rst = db.openrecordset("SELECT cliAddress1, cliAddress2 FROM tblClient
WHERE cliAccountNo = " & cboSelectClient)
txtAddress1 = rst.cliAddress1
txtAddress2 = rst.cliAddress2
 
Back
Top