Setting a ControlSource with a prompt or query... 1

[BFreshour]

I have an Access form and I'm trying to set a ControlSource for an unbound textbox either from prompting the user (just one time) or by querying a table for the information.

I used an OnLoad code that would test to see if txtTeamAbbv.ControlSource = "", if it did it would send an inputbox to the user asking for their abbreviation. It then ran a DoCmd.Save acForm. That seemed to work, but it would never save. Each time the form was loaded it would ask the user, so it obviously wasn't saving the txtTeamAbbv.ControlSource.

Then I thought about having a 'settings' table and running a query to get the first record, teamabbv column and set the ControlSource that way. However I can't figure out how to run a query and get that value...

Can anyone help?

Here is the code I was trying to use:

    If (txtTeamAbbv.ControlSource = "NA") Then
        TeamAbbv = InputBox("What is your team's abbreviation? (3 characters MAX)", "Team Abbreviation", "OKC")
        TeamAbbv = "='" & TeamAbbv & "'"
        txtTeamAbbv.ControlSource = TeamAbbv
        DoCmd.Save acForm, "Offensive Packages"
    End If

 

[hneal98]

May I ask why you want to save the form that way?

 

[BFreshour]

...

Because I'm trying to save that ControlSource so that the user doesn't have to keep entering it everytime they open the form...

 

[hneal98]

Is this for just one user? Perhaps this is a distributed application? If so, I can see where you are going on this.

You could create a .ini file and have Access read from it when it opens the form. You can do a test to see if that value has been read and if it has, then you skip the prompt.

 

[BFreshour]

This is a distributed application. I will try an INI file and see if that works... Thanks.

 

[RoyVidar]

Here's a thread using a table to store the default values thread702-772209.

In a multiuser setup, you could perhaps either have this table locally, or use whatever you use to identify the user as a where condition.

Test for value in the table first, then provide some means of selection (think I'd recommend a combo or something with more validation than an input box though;-))... and store the value for later retrieval.

Roy-Vidar

 

[BFreshour]

Awesome! That's EXACTLY what I needed.