Blank Text Box

[access7]

Can anyone answer the following

I have a text box which runs off a query ... the data in this text box comes from check boxes on a sub form. It is meant to 'auto populate' depending on which check boxes are ticked.

On ticking the check boxes I can see that the information is being saved in both the original table (that the sub form runs from) and the query (which the text box runs off) however - the text box is remaining blank...

If you close the form and go back into the record then the text appears in the text box...

I have written more info on this in another thread called 'Query Result no longer showing in text box'...

Does anyone have any directions to point me in as I'm stumped to how this is happening?

Many Thanks in anticipation...

 

[vbaInet]

You should not be saving calculated values. Here's why:

http://allenbrowne.com/casu-14.html

Also, it's not showing because the record hasn't been saved yet. If you move away from the record to another record then it should show.

However, we don't know what code you are using.

 

[access7]

Apologies, the database was attached to the other post. Please find it attached to this one now, I will have a look at the link you have posted for me.
Thank you

 

[vbaInet]

I just need you to post the expression or code you used in the textbox and the Click event or After Update event of one of the checkboxes.

 

[access7]

This is the only code on the form that relates to the text box

Private Sub CmdInd_Click()

If Me.SubFrm_Industry.Visible Then
Me.SubFrm_Industry.Visible = False
Else
Me.SubFrm_Industry.Visible = True
End If

If Me.CmdInd.Caption = "?" Then
Me.CmdInd.Caption = "X"
Else
Me.CmdInd.Caption = "?"
End If

Me.txtIndustry.Requery

End Sub

The rest is done through the query... the record source for the txt box is Qry_Company and the Control Source is ALLIndustry - which in the query has this expression...

ALLIndustry: IIf([Construction]=True,"Construction, ") & IIf([Engineering]=True,"Engineering, ") & IIf([Health / Education]=True,"Health / Education, ") & IIf([Industrial]=True,"Industrial, ") & IIf([International]=True,"International, ") & IIf([IT]=True,"IT, ") & IIf([Office & Commercial]=True,"Office & Commercial, ") & IIf([Unknown]=True,"Unknown, ") & IIf([Other],[OtherIndustry],"")

 

[vbaInet]

Two things:

1. You are not referencing the subform control. Is the textbox in the subform or the main form?
2. The IIF() function requires three parameters. IIF(Condition, True part, False part). You haven't given a False part.

 

[access7]

The textbox is on the main form (Frm_NewCompany)
Thanks for pointing out that there is no false part, I dont think this has given me issues up to now as if the user does not know the companies industry then it can be left blank. i do have records where it has been left blank and it hasn't as of yet thrown up any error messages...? It may be worth considering putting something in place anyway?

 

[vbaInet]

Then you must reference the subform control. E.g.:

IIf([COLOR=Red][SubformControlName]![/COLOR][Construction] = True,"Construction, ", [COLOR=Blue]Null[/COLOR])

And I've included the False part too.

 

[access7]

Thanks, I will amend my code accordingly.
Can you explain however, as it is confusing me, why this code is working when changing industries (i.e. on Companies / records that already exist) but not working when adding new companies (if you would like to see what I mean then have a look at Frm_Company rather than Frm_NewCompany - essentially these are the same forms, just slight differences in the fact that on the NewCompany form you can edit / add things, rather than on the Company form which is essentially read only until the user clicks the Edit button.
I had more thought it would have something to do with the timings of when things are saved / or even could be another issue I've caused by using the enabled / locked properties in various places??
Again, thank you for your time, it is appreciated

 

[vbaInet]

I will mention a couple of things:

1. Always base your forms (especially subforms) on a query or a SELECT statement.
2. I don't see the need to base your main form's record source on two tables where only one table is related to the fields on the form. You have subforms representing each table, so isolate your tables. That is your main form should be based on the one relevant table.
3. Use the construct given above and refer to the subform control.