change fields together

[Guest]

I'm making a form where a user can add people to a class. The fields on the form are CID (persons ID), First Name(person's first name), LastName (peron's last name), ClassName (name of the class), and Paid (amount they paid for the class). There's a button I put on the form that inserts the selected data into an enrollment table. The CID field is hidden to the user

I have 2 questions

1) I want to combine the FirstName and LastName fields together, so when you pull the drop down list it is displayed as "Last, First" and sorted ascending by last name. How do I do this

2) How can I link the CID and name fields together so that when the name is selected, the CID field changes and displays the correct CID

Thanks in advance; any help would be greatly appreciated

Kyle

 

[Gerald Stanley]

Assuming that you will be using a comboBox,
1. Make the RowSource SQL something like
SELECT [Last Name] & ", " & [First Name], CID FROM
YourTable ORDER BY [Last Name] , [First Name]
2. Set the ColumnCount to 2
3. In the ComboBox's AfterUpdate eventHandler, put in code like
YourCIDTextBox.Value = YourComboBox.Column(1)

You will have to change YourTable, YourComboBox and
YourCIDTextBox to suit your app.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I'm making a form where a user can add people to a class.

The fields on the form are CID (persons ID), First
Name(person's first name), LastName (peron's last name),
ClassName (name of the class), and Paid (amount they paid
for the class). There's a button I put on the form that
inserts the selected data into an enrollment table. The
CID field is hidden to the user.

I have 2 questions:

1) I want to combine the FirstName and LastName fields

together, so when you pull the drop down list it is
displayed as "Last, First" and sorted ascending by last
name. How do I do this?

2) How can I link the CID and name fields together so

that when the name is selected, the CID field changes and
displays the correct CID?

 

[Adrian Jansen]

1)
Make a query containing the CID, FirstName and LastName. Add another field
to the query grid with something like

FullName:FirstName & "," & LastName

This gives you a full name field with the first and last names combined, and
a comma separating them - you can use any formatting you like here.
Set the sort order in the query to LastName
Use the query as the row source of your combo box, and include at least the
CID and FullName fields.
Set the column width of the CID to zero, so the combo box only shows the
fullname column.

2)
For example, if the control names are txtCID for the CID field, and
cmbFullName for the combo box, then in the Click event of the combo box, use

me!txtCID = me!cmbFullName

Then whenever the combo box selection is made the CID shows up in the text
box.




--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

I'm making a form where a user can add people to a class. The fields on

the form are CID (persons ID), First Name(person's first name), LastName
(peron's last name), ClassName (name of the class), and Paid (amount they
paid for the class). There's a button I put on the form that inserts the
selected data into an enrollment table. The CID field is hidden to the
user.

I have 2 questions:

1) I want to combine the FirstName and LastName fields together, so when

you pull the drop down list it is displayed as "Last, First" and sorted
ascending by last name. How do I do this?

2) How can I link the CID and name fields together so that when the name

is selected, the CID field changes and displays the correct CID?

 

[Guest]

Thanks Gerald, I was able to get my first issue to work properly, however, I'm having a little trouble with the changing the CID number to match the Name in the dropdown list

I put

Text9.Value = LastName.Column(1

into the AfterUpdate event on the Name dropdown list. When I try to select a different name, access gives me an error that it cannot find the macro "Text9

What am I doing wrong

Thanks

Kyl

----- Gerald Stanley wrote: ----

Assuming that you will be using a comboBox
1. Make the RowSource SQL something lik
SELECT [Last Name] & ", " & [First Name], CID FRO
YourTable ORDER BY [Last Name] , [First Name
2. Set the ColumnCount to
3. In the ComboBox's AfterUpdate eventHandler, put in code lik
YourCIDTextBox.Value = YourComboBox.Column(1

You will have to change YourTable, YourComboBox an
YourCIDTextBox to suit your app

Hope This Help
Gerald Stanley MCS

-----Original Message----
I'm making a form where a user can add people to a class

The fields on the form are CID (persons ID), Firs
Name(person's first name), LastName (peron's last name)
ClassName (name of the class), and Paid (amount they pai
for the class). There's a button I put on the form tha
inserts the selected data into an enrollment table. Th
CID field is hidden to the usertogether, so when you pull the drop down list it i
displayed as "Last, First" and sorted ascending by las
name. How do I do thisthat when the name is selected, the CID field changes an
displays the correct CID

 

[Guest]

Thanks for the help, it appears to be a good solution...I am having one problem thoug

I made the query and modified the design so it displays as I need it to. But, when i try to use the form, the names appear in the drop down list, but when I click on them, i get a "windows ding" and no error message. It does not allow me to select any of the names? What's going wrong

Thanks

Kyl

----- Adrian Jansen wrote: ----

1
Make a query containing the CID, FirstName and LastName. Add another fiel
to the query grid with something lik

FullName:FirstName & "," & LastNam

This gives you a full name field with the first and last names combined, an
a comma separating them - you can use any formatting you like here
Set the sort order in the query to LastNam
Use the query as the row source of your combo box, and include at least th
CID and FullName fields
Set the column width of the CID to zero, so the combo box only shows th
fullname column

2
For example, if the control names are txtCID for the CID field, an
cmbFullName for the combo box, then in the Click event of the combo box, us

me!txtCID = me!cmbFullNam

Then whenever the combo box selection is made the CID shows up in the tex
box




--
Regards

Adrian Janse
J & K MicroSystem
Microcomputer solutions for industrial contro

I'm making a form where a user can add people to a class. The fields o

the form are CID (persons ID), First Name(person's first name), LastNam
(peron's last name), ClassName (name of the class), and Paid (amount the
paid for the class). There's a button I put on the form that inserts th
selected data into an enrollment table. The CID field is hidden to th
useryou pull the drop down list it is displayed as "Last, First" and sorte
ascending by last name. How do I do this

 

[Adrian Jansen]

Sounds like your form has the "allow edits" property set to False.
Right-click, and check in the properties list under the Data tab. Also
check the enabled and locked properties of the combo box.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Thanks for the help, it appears to be a good solution...I am having one problem though

I made the query and modified the design so it displays as I need it to.

But, when i try to use the form, the names appear in the drop down list, but
when I click on them, i get a "windows ding" and no error message. It does
not allow me to select any of the names? What's going wrong?

 

[Gerald Stanley]

Kyle,
Access seems to be saying that you have no textbox called
text9 on your form. Try putting Me. before it and see if
text9 appears in the dropdown list of properties. One
thing I should have pointed out yesterday was that, if you
set the comboBox's BoundColumn to 2, its value will be that
of the CID which should negate the need for a separate text
box anyway.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Thanks Gerald, I was able to get my first issue to work

properly, however, I'm having a little trouble with the
changing the CID number to match the Name in the dropdown list.

I put:

Text9.Value = LastName.Column(1)

into the AfterUpdate event on the Name dropdown list.

When I try to select a different name, access gives me an
error that it cannot find the macro "Text9"

What am I doing wrong?

Thanks,

Kyle

----- Gerald Stanley wrote: -----

Assuming that you will be using a comboBox,
1. Make the RowSource SQL something like
SELECT [Last Name] & ", " & [First Name], CID FROM
YourTable ORDER BY [Last Name] , [First Name]
2. Set the ColumnCount to 2
3. In the ComboBox's AfterUpdate eventHandler, put in code like
YourCIDTextBox.Value = YourComboBox.Column(1)

You will have to change YourTable, YourComboBox and
YourCIDTextBox to suit your app.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I'm making a form where a user can add people to a

class.
The fields on the form are CID (persons ID), First
Name(person's first name), LastName (peron's last name),
ClassName (name of the class), and Paid (amount they paid
for the class). There's a button I put on the form that
inserts the selected data into an enrollment table. The
CID field is hidden to the user.together, so when you pull the drop down list it is
displayed as "Last, First" and sorted ascending by last
name. How do I do this?that when the name is selected, the CID field changes and
displays the correct CID?

.

.

 

[Eric Cárdenas [MSFT]]

I'm making a form where a user can add people to a class. The
fields on the form are CID (persons ID), First Name(person's
first name), LastName (peron's last name), ClassName (name
of the class), and Paid (amount they paid for the class).
There's a button I put on the form that inserts the selected
data into an enrollment table. The CID field is hidden to
the user.

I have 2 questions:

1) I want to combine the FirstName and LastName fields together,
so when you pull the drop down list it is displayed as
"Last, First" and sorted ascending by last name. How do I
do this?

2) How can I link the CID and name fields together so that when
the name is selected, the CID field changes and displays the correct
CID?

Thanks in advance; any help would be greatly appreciated.

Kyle

--------------------
There is a fundamental problem with this approach: people's names are not
unique. What if you have two John Smith's in one class? How do you resolve
that?

I suggest that you always display the CID so that the unique identifier is
always visible no matter if people's names are duplicated.

Hope this helps,