Assign an unbound textbox a value in a table

I think a table is the best way to go with this. We do this with many applications.
The table should have just one record in it and you can even make the field a lookup type with a combo box with the list of facilities the user can choose from. I would make a form/report based upon this table and set the Add Records and Delete Records property to false so the table will just maintain one record which can be edited. This formis what the user would use to update the facility name. You could have them update the table directly but it is critical to only have one record in this table and using the form will control that because of the Ad/Delete settings I mentioned.
Then create another form/report based upon that table and put the facility feild name in the detail section of the form/report. This will be used to display the facility name all forms and reports. Place it as a subform/subreport in the header section of any reports/forms you create and the facility name will be displayed at the top.
Note, you do not need to create a relationship between the subform/subreport and the main form/report since there is only one record in the facility table.
The public variable idea is not bad but I found storing it in a table is a bit simpler and more reliabie.

You could also use a database property.  You can create a property "FacilityName" by runnining the following in the Immediate Window.

Set prp = currentdb.CreateProperty("FacilityName", dbText, "default")
currentdb.properties.Append prp

Then, in your startup form, you can check to see what the value of this property is using:

Dim strFacName as string
Dim intResponse as integer

if currentdb.Properties("FacilityName").Value = "default" Then
    Do
        strFacName = Inputbox("Enter your facility name")
        intResponse = msgbox("You entered:" & vbcrlf & vbcrlf & strFacName & vbcrlf & vbcrlf & "Is this correct?", vbYesNoCancel)
        if intResponse = vbCancel then
            Exit Sub
        Elseif intResponse = vbYes then
            Exit Do
        endif
    Loop

    currentdb.properties("FacilityName").Value = strFacName

End If

Then, whenever you need this value, all you have to do is:

    me.Caption = currentdb.properties("FacilityName").Value

or something like that.

<No points wanted>

<hate to waste a table for just one value>
True, but only a table value will "persist" after you close the DB.

This is no big deal really, this is done all the time to hold User specific settings...

JeffCoachman

Another method is to include the facility name with the database like:

Database1_Facility1.ACCDB
Then, in the startup, store it in a public variable, like:

 ' I have to check the syntax if you decide to use this method.
strFacility = <Mid(Instr(CurrentDB.Name,"_")+1, CurrentDB.Name)> 'remove Database1_ string
strFacility = <Left(strFacility,Inst(CurrentDB.Name,"."-1)> 'remove .ACCDB

If Dcount("*","tbaleFacility","FacilityName='" & strFacility & "'")>0 Then
'facility name exists in the database

Else
'facility name doesn't exist in the database
'infor the user and ask the facility name to be corected or...
End if

in my last post, strFacility as a public variable is available for use elsewhere.

correction...
.
.
Else
'facility name doesn't exist in the database
'infor the user and ask the facility name included with the database to be corected or tell there is no data for this facility.
End if

^
eghtebas, slick...
;-)

Jeff

I like the idea of using a combo box in my admin page. I could hard code the facility values as these will not change (about 13 facilities) and have the user (read administrator) select the appropriate value to populate the table's filed.

The question is this. I know how to hard code the values for the combo box but how do I both write what was selected to the field and how do I code my textbox (that I plan to place in the header of each form) to read that field value from the table?

Thanks!

Hi Jeff,

Thanks, but a better solution is to have a splash screen where all the facilities are presented via a combo box for the user to choose from and then to store it in a table as the other experts and you have sugested it.

There could be a check box [ ] Do not show Splash Screen that when checked, it will not open.

On another form (the second form after the splash screen), there could be a button labled 'Choose Another Facility' which upon click opens the spash screen with the check box unchecked.

This way the tblSettings will have two fields: CurrentFacility (text), BypassSplashScreen (Y/N)

Upon opening of the database, if thereis no facility entered to tblSettings, BypassSplashScreen needs to be unchek (just incase) and force the splash screen to open.

If there is a facility and the check box is checked, the splash screen to be bypassed.

Mike

or ...

CurrentDB.Execute "Delete * From tblSettings"
CurrentDB.Execute ("Insert Int tblSettings(Facility) Values('" & cboFacility.Column(0) & "'")

And...in the control source of the text box on a form or a report, have

=Dlookup("Facility","tblSettings")

eghtebas,

Thanks...I'll check this when I get a break today.

Thank you again.

Mike

I still disagree that you need to use a table for this, or that a table is the only way to persist the value.

Use the database property; this is where I store most of my application level values.  If you must, create a form for selecting the appropriate facility from a list, and instead of using the inputbox mentioned in my previous message, check to see whether the property is set to "default" and if so, then open the form with the combo or listbox to display the facilities, then save the selected value back to the database property.

eghtebas:

I get the following error message.


screenshot.png

shogun5,

"My initial question was how do I read a value from a field into a textbox (and then I would place that textbox on all forms and report headers."

This might be the best approach you wanted but I just attached the db for you to look into.

The db works on this way:
1. User must choose the facility name. Once chosen, the form hides and whatever value on that combo box, it will be used by all forms and reports within the DB.


Sincerely,

Ed
Sample-Combo.mdb

MINDSUPERB,

Thank you but the problem with this approach is that the value is persistent and as soon as the splace form is closed the facility value is gone.

shogun5,

The splash form is only hidden. You can unhide it through a command from any form if you want so that you can choose another value. And once the value has been chosen, the value in the combo box is always there unless of course if you will close the Splash form.

Ed

Thanks! I was able to figure out the rest with this code.

My apology... I was away for a while and didn't have the opportunity to respond.

re:> My initial question was how do I read a value from a field into a textbox...

Me!txtFacility = Dlookup("Facility","tbaleFacility")

In on open event of any form or report will read its value on put on the txtFacility you have on your form/report.

Mike