Solved

Assign an unbound textbox a value in a table

Posted on 2010-09-19
19
1,244 Views
Last Modified: 2012-05-10
Experts,

Not sure a table is the way to go here but I have a database that will be used by different facilities. I would like to store the facility's name in either a table (hate to waste a table for just one value) or create a global variable somehow where I could call upon this variable in a form and report. That way when a facility gets the database they just change this variable and all the forms and reports will have the 'that' facility name displayed.

Any ideas?

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. But I am open to other suggestions.

Thanks!
0
Comment
Question by:shogun5
  • 7
  • 5
  • 2
  • +3
19 Comments
 
LVL 3

Expert Comment

by:AccessYourBiz_Com
ID: 33712877
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33713097
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33713100
<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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33713124
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33713139
in my last post, strFacility as a public variable is available for use elsewhere.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33713154
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33713239
^
eghtebas, slick...
;-)

Jeff
0
 

Author Comment

by:shogun5
ID: 33713249
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!
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33713280
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 33713300
to write from combo box:

MsgBox cboFacility.Column(0)  'test to make sure it is reading facilty name ok

If Dcount("*","tblSettings")>0 The
   'there is at leas on record in it. It is possible to have zero records
   CurrentDB.Execute ("Update tblSettings Set Facility='" & cboFacility.Column(0) & "'")
Else
   CurrentDB.Execute ("Insert Int tblSettings(Facility) Values('" & cboFacility.Column(0) & "'")

End IF
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33713305
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")
0
 

Author Comment

by:shogun5
ID: 33713342
eghtebas,

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

Thank you again.

Mike
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33713484
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.
0
 

Author Comment

by:shogun5
ID: 33713658
eghtebas:

I get the following error message.


screenshot.png
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33713960
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
0
 

Author Comment

by:shogun5
ID: 33714032
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.

0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33714052
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
0
 

Author Closing Comment

by:shogun5
ID: 33724163
Thanks! I was able to figure out the rest with this code.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 33790153
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



0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now