Information label on form

[jwleonard]

I am attempting to place a label on the top of a tabbed form and I am having a problem. Here are the important parts of my database:

tblInstructors
InstructorID Autonumber PK
RankID FK to tbl Ranks
LastName
FirstName

tblRanks
RankID Autonumber PK
Rank

I have a tabbed form with a combo box for RankID and text boxes for FirstName and LastName. Obviously there is more information than this on this form, it is tabbed and I would like a label on top of the form that shows the name and rank of the person's record that is being edited or viewed while the user is on another tab. Normally I would use =[RankID] & "" & [FirstName] & "" & [LastName], however this isn't working since the RankID control is passing the Autonumber from tblRanks instead of the actual rank (yes this is military rank abbreviation). For example I get the number six instead of TSGT. I need to find a way to show the text value instead of the autonumber that represents it.

I have searched to the best of my abilities to find anything on this forum about this as I am sure it has come up before but have been unable to find anything.

 

[RuralGuy]

Make sure these fields are available in the underlying query.

 

[databasedonr]

If the appropriate field is also on another tab, refer to that field on your active tab. For example, on the first tab of my form (named frmMemberInfo) I have the fields Surname, Given1 and Rank; on a secondary tab, create a text box, (change the background so it looks like a label) and reference it thus: =[Forms]![frmMemberInfo].[Surname] etc. I use this in several occasions and it works very well. I usually concatenate it so that I can control the spacing, so my actual source of the textbox would be:

=[Forms]![frmMemberInfo].[Rank] & " " & [Forms].[frmMemberInfo].[Given1] & " " & [Forms].[frmMemberInfo].[Surname]

Give that a try -- as long as these fields are in your query and on the first or main page of your form, this will work.

 

[jwleonard]

Well, that would explain my problem then. I was trying only use the table as my record source since all of the data I need for this form is in one table with lookups to other tables (kind of). Anyway I see how using a querry would fix it! Thanks

Now my curiosity has me wondering if there is a way to reference a field in a table from a form, report, or querry though? My first instinct is =Tables![tblName]![FirstName] (This is just an example, it has nothing to do with my db anymore) but that doesn't work. Is there a way to do this?

 

[jwleonard]

If the appropriate field is also on another tab, refer to that field on your active tab. For example, on the first tab of my form (named frmMemberInfo) I have the fields Surname, Given1 and Rank; on a secondary tab, create a text box, (change the background so it looks like a label) and reference it thus: =[Forms]![frmMemberInfo].[Surname] etc. I use this in several occasions and it works very well. I usually concatenate it so that I can control the spacing, so my actual source of the textbox would be:

=[Forms]![frmMemberInfo].[Rank] & " " & [Forms].[frmMemberInfo].[Given1] & " " & [Forms].[frmMemberInfo].[Surname]

Give that a try -- as long as these fields are in your query and on the first or main page of your form, this will work.

This is pretty much what I was doing, but since my combo box bound column is acutally the autonumber instead of the rank abbreviation that goes with it, I am passing that to my label (or textbox) instead. The user sees TSGT in the combo box but it passes the number 6 since that is what they are really selecting.

 

[ShaneMan]

Hey jwleonard,

I'll give it a shot. If the [FirstName] and [LastName] fields are text fields on your form already and your cboBox has RankID as the bound column and the next column is Rank, then how about trying this.

You can decide where to put this and you probably would need to check each field for Null.

Me.lblName.Caption = "Rank " & Me.cboBoxName.Column(1) & " " & Me.FirstName & " " & Me.LastName

 

[jwleonard]

Hey jwleonard,

I'll give it a shot. If the [FirstName] and [LastName] fields are text fields on your form already and your cboBox has RankID as the bound column and the next column is Rank, then how about trying this.

You can decide where to put this and you probably would need to check each field for Null.

Me.lblName.Caption = "Rank " & Me.cboBoxName.Column(1) & " " & Me.FirstName & " " & Me.LastName

I am trying to avoid using too much code since someone with even less experience than me may have to work on this database in the future. This does work but is there a way to do it in access instead of code so it is easier for someone down the road to figure out?

I do appreciate the help so far though, thanks.

 

[ShaneMan]

You could change from a label to a text box and put the code in the text box control source. I would recommend changing the text box Enabled to No and Locked to Yes and TabStop to No.

Shane

 

[jwleonard]

You could change from a label to a text box and put the code in the text box control source. I would recommend changing the text box Enabled to No and Locked to Yes and TabStop to No.

Shane

I was unable to get the code to work in the control source of the text box, it kept changing my code by putting brackets around it. I think I will just use the VBA option, thanks again.

 

[Rich]

=[YourCombo].Column(1) where column 1 is the second column of your combo box as the control source of your textbox

 

[jwleonard]

Rich, did you mean to put that in the label (or textbox) or are just correcting the VBA code that ShaneMan posted.

 

[Rich]

as the control source for an unbound textbox on your form

 

[jwleonard]

When I type =[cboRankID].Column(1), access automatically converts to this; =[cboName].[Column](1)

 

[Rich]

Does it work, if not how many columns are in your combo.

 

[jwleonard]

No it doesn't work. I have two columns, the combo is bound to column one which is the RankID field (the autonumber that I don't want to show), column widths are set to 0";1" so the user only sees the second column witch is the Rank column that I want the user to see.

I can't even get any of the VBA options that others suggested to work correctly!

 

[jwleonard]

Well I found my problem, I had messed around with an activeX calendar, then decided it was very buggy and removed it and all files associated with it. However, I checked the references in VBA and it was still listed, this actually stopped all of my code from running properly even though I can't explain why (like I said very buggy). As soon as I removed that reference everything started working correctly. Thanks for all the help!

 

[ShaneMan]

Your welcome JW. Glad you stayed with it and got it to working.

Shane