how to store two different values

[WB786]

I want the user to be able to use the drop down to list the last name and first name of an employee from the employee table. Which I have done using a query. But the value should only be stored as Lastname, Firstname combined in one column of the table not just the first name or just the last name or employee id like it is doing now.

Thanks in advance,
Waheed

 

[allanon]

Here is what I would suggest. Place a field on your form named 'FullName'. Assign this field to your table or query column. On the click event of the listbox put something like

me.FullName.value = me.listbox.lastname column + ", " + me.listbox.firstname column

You can make the FullName field visible property = False so the users don't see this.

There may be a better way but that should work fine.

 

[WB786]

Thank you for the tip but now I am stuck on this error. My code is like this:

Me.FullName.Value = Me.Counselor.FirstName + Me.Counselor.LastName

Where the name of the list box is Counselor and the counselor table has values of FirstName, LastName, Extension, and ID but I am only selecting FirstName and LastName for the list box to show.

On click I am getting compile error: Method or data member not found and FirstName is highlighted. I hope you can provide some more help to this poor network admin who is told to design a database and I got a lot of learning to do. Thanks again.

 

[GeekinGlasses]

it should be formated like this

Me.FullName.Value = Me.Counselor.Column(#) + Me.Counselor.Column(#)

the # equals the column number of the information you want passed form the listbox. FYI, the first column in a list box is 0 (zero) not 1

 

[WB786]

Awsome IT WORKED!!! Thanks for everyone that is helping this poor soul out. But here is my next problem with this. The field is not bound as FullName is bound to the Counselor record. The value is getting stored into the correct table and under the correct column with both the first name and last name like it should, but when I am flipping through the record the unbound field is blank and the FullName field shows the data of course. Is there a way for the nonbound field to show the counselors name as well. The FullName field is going to be hid so the Counselors field need to show the stored value.

Getting there, Thanks
Waheed

 

[rarich01]

I'm confused. You have to be a little more specific. Which field is not bound? Do you have a field called FullName in your counselor's table? Where is the 'LastName + FirstName' value being stored? What does this mean: "when I am flipping through the record the unbound field is blank and the FullName field shows the data of course"?

 

[WB786]

Sorry but I don't know how else to explain it: The dropdown box (pulls info from the counselors table) that displays the counselor's name and is not bound as the FullName field is combining the FirstName + LastName columns and storing the value into the InfoTable under Counselor. If I bound the Counselor Drop down field to Counselor table field then I can I see the stored value when I click on previous record. But get an error saying somthing like make sure the value entered is not number in text field and vise versa. If the counselor field is not bound then no problem. After filling out the form with all other proper info and if then user wants to look at some previous records then the counselor field is blank but I want it to show the stored value that was orginally chosen. Since it is not bound to anything then no value shows up.

The other option would be to leave the FullName field non-hidden and positioned under the counselor's name field and that way when the user is flipping through the records the counselor field will be blank but the FullName field will show the stored value in the InfoTable. The database is totally functional but I don't like the look and don't want the user to get confused. Thanks.

 

[GeekinGlasses]

It would seem that if you wanted it to work, then You would have to know when the navigation buttons is being pressed to change the record (I'm assuming that's what you mean by flipping through the records) I don't know of anyway to capture when an Access Nav button is pressed. Your idea of seeing through the combo box when it's blank to the fullname field below is do-able. But I think a more effective way is to control the "flipping through of records" by not using Access Nav buttons, but making your own nav buttons, with .movenext command on the buttons click event.

If you didn't mean pressing the Nav buttons is "flipping through the records", then please explain further.

 

[WB786]

GeekInGlasses,

No you are right. Flipping means the little arrows on the bottom of the form that come with Access which as you point out might not be a good idea to have. I will turn them off and putt the record nav buttons using the wizard. So you are saying if I do this then I can code it to say on click event update the unbound field with the properly stored value that should be there and if that is possible then can you help me out on the coding of it. I know I shouldn't spent too much time on this very little piece of the big pie and I am sure I can train the users not to worry about the field as it will be empty but the field called fullname below will have the proper counselor name showing but would still like to know if it possible to do such and how?

Thanks again. You have been very helpful.
-Waheed

 

[Guest_imported]

Why don't you just use the drop-down list as your bound control and get rid of the other FullName control. You can join the FirstName and LastName in your query that the drop-down list uses. The first field of your query should be the CounselorID of the Counselors table and the second should be FullName:[LastName]&", "&[FirstName] The first column of the drop-down list should be hidden by setting it's width to 0.

The Counselor field in the InfoTable should be a number which will store the CounselorID. The drop-down list should be bound to this field. Now the drop-down list will display LastName, FirstName and you don't have to write any code. This way you are only storing the counselor's name once in your database, so for instance if you had a spelling mistake in a counselor's name you would only have to correct it in the Counselors table and all records in the InfoTable with that name would also be update.

Also, if you want to run some code each time you move to a different record, use the OnCurrent Event of the form.

Hope this makes sense.

 

[WB786]

dermotfitz that was a very good way to do it. Makes life a lot easier. But I am still getting an error: The value you entered isn't valid for this field, For example, you may have entered text in a numberic field or a number that is larger than the FieldSize setting permits. I have setup the query the way you said and it pulls the info from the table it should. When you are setting up the drop down list options do you chose both field from the query: id and fullname? I still want it to show the FullName but store the value as numeric and I think that where my problem is as it trying to store the text and not number like it should.

Thanks,
Waheed

 

[Guest_imported]

Yes the you should choose both the ID and the FullName when setting up the drop-down list. The drop-down list should have 2 columns. the first one should be the ID and the second the FullName. Make the first one hidden by setting its width to 0. Make sure that the BoundColumn property of the drop-down list is set to 1. I think that is where you are going wrong.

Good look!

 

[GeekinGlasses]

What I meant earlier wason't Use the Navigation buttons, code your own. A simple function would be:

Function MoveToRecord (Direction as String, NumberToStep as Integer) as Long

Dim dbs as Database
Dim rst as Recordset
Dim i

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("RecordNameGoesHere&quot

With rst
for i = 1 to NumberToStep
if UCase(Direction) = "FORWARD" then
.movenext
if .eof then
.movelast
end if
end if
if UCase(Direction) = "BACKWARD" then
.moveprevious
if .bof then
.movefirst
end if
end if
if UCase(Direction) = "FIRST" then
.movefirst
if Ucase(Direction) = "LAST" then
.movelast
next i
end with
MoveToRecord = Me.CurrentRecord

dbs.close
set dbs = nothing
rst.close
set rst = nothing

end function


Ok, then create buttons like nav buttons; Create a left arrow, right arrow...and so on, with a textbox in the middle. Set the textbox control source to Me.CurrentRecord

Then use each buttons On Click event to run the function.

 

[WB786]

I would like to say BIG THANKS to everyone that has helped me achive my goal. The database is looking like the way I wanted it as far as the functionality of it. My BOSS is happy and that is all that matters sometime. This site and all the people are just AWSOME!!!!!