Text box issue

[NavyJoe]

Hey I have a text box that I want to automatically update once you click a combobox. I want it to the be long name of the award in the combobox.

Combobox1 (Award)
JSAM
JSCM
DMSM
DSSM

Textbox1 (Award Long)

I have a table that has this information on it. I tried making the textbox a combo box, but it doesn't come up automatically, I have to choose the only option. I tried an IIf formula on the OnChange Event of the Combobox Awards, nothing.

Here's an example

User clicks on Award Combobox and chooses JSAM. I want the Award Long box to automatically say Joint Service Achievement Medal. How can I do this.

 

[blueshark]

It can be done by syncrhonizing subforms. Make the subform source equal to the combo value (ie SELECT Long WHERE Short=ComboBox)

I know it seems pretty wasteful for just one control. If you have the Northwinds sample db there is a good example in there about updating a subform based on the value of another form.

 

[Norie]

Joe

Is the textbox unbounded?

If it is you could use a DLOOKUP to populate it. Perhaps something like this:

Private Sub Combo2_Change()
    Me.Text0.Value = DLookup("[Award Long]", "Awards", "Award='" & Me.Combo2.Value & "'")
End Sub

 

[NavyJoe]

No it represents a field in the award table.

 

[NavyJoe]

This is what I've done.

I created a button that opens a subform that holds all the information I want automatically updated. The button also Requery's the textbox control sources. This is the code for the button:

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "SSS Verification Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command80_Click:
    Exit Sub

Err_Command80_Click:
    MsgBox Err.Description
    Resume Exit_Command80_Click
    
Forms![SSS Verification Form]!Processor.Requery
Forms![SSS Verification Form]!AWARD_LONG.Requery
Forms![SSS Verification Form]!RANK_SHORT.Requery
Forms![SSS Verification Form]!SUBJECT.Requery
End Sub

And this is the codes in the 4 text box control sources:

Processor:

=(SELECT  [Processors]![Name] WHERE [Processors]![Initials] = Forms!Award Log]![Processor] )

Award Long:

=(SELECT [Awards]![Award Long Name] WHERE [Awards]![Award Short Name] = Forms![Award Log]![Award])

Rank Short (this one is determined by two different comboboxes (1 table)):

=(SELECT  [Rank Chart]![Paygrade] WHERE [Rank Chart]![Branch] = Forms![Award Log]![Branch] And WHERE [Rank Chart]![Rank]=Forms![Award Log]![Rank] )

Subject is a bit complicated as it requires these blocks.

My issue is the form when opened up didn't requery the fields. So I tried a creating a button on the form itself just to requery the fields, that didn't work either. Please help.

 

[tonnic]

Hi,

with following statement you cant requery the controls:

On Error GoTo Err_Command80_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SSS Verification Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
MsgBox Err.Description
Resume Exit_Command80_Click

Forms![SSS Verification Form]!Processor.Requery
Forms![SSS Verification Form]!AWARD_LONG.Requery
Forms![SSS Verification Form]!RANK_SHORT.Requery
Forms![SSS Verification Form]!SUBJECT.Requery
End Sub

First: If you let this code, the controls on the form will never requery, because on the red mark you always exit sub.
Second: You dont need to requery, because on open the form requeries automatically. Also your Where-Condition in the openform-function is empty/null because you dont initialize your stLinkCriteria - so you cant get other values on the form...

 

[NavyJoe]

The codes are in the control source of each textbox. Not in the code builder. How do i "initialize the stLinkCriteria".

 

[tonnic]

'vars
dim stLinkCriteria as string

'init vars
stLinkCriteria = "ColumnA=500" 'or if you have the criteria in an textbox then :
stLinkCriteria = me.textbox.value

now the var stLinkCriteria has the value "ColumnA=500"

Based on your example:

docmd.openform DoCmd.OpenForm stDocName, , , stLinkCriteria

Now the form would only show Records where the value in ColumnA is 500.
You have to change ColumnA with the name of the control you want to filter.

 

[NavyJoe]

This is the hot issue for me. Once I get this coding worked out, I'm finished with this part of the database. I can then move on to the Report. Anyway. I used a list box and it worked great. But the data in the field wouldn't show up in the Subject field (like discussed in another topic). I figured that part out. If I clicked on the fields, they would show up in the Subject field. The reason is cause it's a list box and I have to select the answer. So, I need these fields to be text boxes. If you could please show me how to do this, that would be great. For all those that don't know:

Form1:
Rank Long (combo box that is linked to Rank.Table)
Award Short (combo box that is linked to Award.Table)
Button (once clicked, this saves record and opens Form2)

Form2:
Rank Short (need this to be a text box that equals Rank Long data in the table..i.e. Staff Sergeant = SSgt)
Award Long (need this to be a text box that equals Award Short data in the table...i.e. JSAM = Joint Service Achievement Medal)

Award.Table
Award Short
Award Long

Rank.Table
Branch (Branch)
Rank (Rank Long)
Paygrade (Rank Short)