Access Database - I need to select a record in the database and have it display in a text field on a form after it is selected.

I have a table with a Yes/No selection that has two records in it - they designate the company to select for a query I am running - I want to be able to display the selected Company ID on the form (which is from the Query of the table - where the Select field is Yes

Select   COID   Comapny NAme
Yes        CO1        Company 1
 No        CO2        Company 2

Open the table with a button - select which company - using the check box - then display the company ID & Name in a field on the form - Normally will remain that same company - so it does not have to always be re-selected - just needs to change when the different comany is selected.  Not sure about getting the "display" field to change when the selection  changes.
cbishopcgrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I'm not totally sure I understand your question.

You could use an unbound combo to select the company.  You can control what remains visible when the list part closes by hiding the ID column and only showing the company name.
0
cbishopcgrAuthor Commented:
I want to be able to select which company to use in the rest of my data - so when I use the query for the company - I am looking to see if it is YES in the selection - that all works - and if it was already selected - I can get it to show in that field - but when I change it from a button on the form - it is changed for the query - I just cannot get it to display in the text box on the form - it still shows the previous selection.

I am not really a programer - but have been building this mini application in Access to take some imported data - compare it to tables I have created and export something to excel for import into another system.

In all of the cases - the data is based on the company code  - which I thought was originally going to be one - but then I turned out that it might need to be more than one down the road - so I am trying to cover that now.

Long story I know - just thought if you had an idea of what I was doing it would help.  I have a table similar to what I first outlined - and just want a way of opening the table from the form - selecting the company, then when I close it - the field that showed what company is selected would change - so maybe it is a focus thing or something like that.
0
AccessGuy1763Commented:
It sounds like you're trying to use a field named "Select" as a way for your users to "choose" between the two records in your form.  Not that it couldn't work, but you'd see most people go with a design more like what Pat is suggesting where you display a combobox of the selections and then "do stuff" when the value in that combobox is changed by the user.

What "do stuff" is in your case is kind of vague right now.  I think you just want the form filtered to that record, in which case I would make the combobox unbound and just filter to the appropriate COID as they change the combobox.  If you can provide a little more detail, it would be easier to help you with the specific elements of the design to accomplish what you're looking for.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cbishopcgrAuthor Commented:
Ok - I have attached an excel that I have a tab that shows what is in the Company ID table with some screen shots of the Form - so what I have is a button that opens the table so that I can select the company I want to use - I also have a text field that displays the company ID that is selected (this is off of the Query I have in the second tab - basically the one that is selected from the CompanyID table.

I want to select the company and have it show the selected company in the text box - so when I select the other company -that field will reflect the change.  It would already in the query - I just do not know exactly how to do this on the form.

I am sure there may be easier ways to do this - just need to figure out the best way to do what I am trying to do.  Hope the attachment helps show what I need.
CompanyID-Select.xlsx
0
AccessGuy1763Commented:
I think the main problem you're running into here is that the "CompanyID" field is bound.  Are you utilizing this form as a way to select a Company and do certain exports and other tasks with it or are you actually intending to use the form itself as a way to alter/edit your records?  I think the answer is the former, in which case you need to delete the control source for that field, change it to a combo box, and set the RowSource to show the appropriate information so that your user can select a Company.  The query can then be changed to refer to the combobox (on the basis of CompanyID) so that it filters down the records properly rather than the "Select" field.

So... the Criteria for the CompanyID field in your query should be something like this:

Forms!frmYourGLFormName!comboYourComboBoxName

This presumes CompanyID is the first column selected in the RowSource of the combobox.
0
cbishopcgrAuthor Commented:
That does work - is there a way that I can "save" that selection?  Meaning that they most always will select one company and I would like that to be the one that remains there so the do not have to reselect it each time....maybe not necessary - but would make it easier.
0
AccessGuy1763Commented:
Are you talking about having the user utilize the form and having Access remember their last selection even after closing that form and then reopening it, or do you just want it to "remember" during a single "session" of the form?
0
cbishopcgrAuthor Commented:
remember it from their last selection even after closing that form and reopening it.

kind of like the default or the last company selected (they most always use the only the SSB company)
0
AccessGuy1763Commented:
Well, default and the last company they selected are different.  Default is cake... just set the "Default" property on the Data tab of the combobox property sheet.

For "last selection", I would handle it with a global variable.  You would need to add a declaration like this to a standalone/global module within your database:

Dim lngGLFormLastSelectedCompanyID as long

It being a variable with global scope, you will be able to store and retrieve values at any time, including after the form is closed.  Next you need to add something like this your combobox's Change event so that the global variable is kept up to date as your user changes their selection:

Private Sub cmboMyComboName_Change()

   lngGLFormLastSelectedCompanyID = me.cmboMyComboName

End Sub

Open in new window


Next I would add something like this to the form's Load event:

Private Sub Form_Load()

   If lngGLFormLastSelectedCompanyID <> 0 then

      me.cmboMyComboName = lngGLFormLastSelectedCompanyID

   End if

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cbishopcgrAuthor Commented:
Will the info above be maintained even after the whole Access database is closed?  There is really not that much maintained in the database - I am linked to some tables and have just a few that are actually in this database - it is used more as a utility they may open and run every few days or maybe once a week.
0
cbishopcgrAuthor Commented:
alslo - just to make sure - when I am complete - do I Accept Multiple Solutions or just the one that says Accept as solution - want to make sure you get credit for it.
0
AccessGuy1763Commented:
If you're awarding all the points to one person I wouldn't bother picking multiple solutions... just pick the most relevant post from that person and they'll still get all the points.

This particular solution would not get saved when the database is closed.  The scope of the global variable starts when the database is loaded and ends when it is closed.  Saving the preference between whole database sessions will be considerably trickier because you would need to not only store the value (I would just store it in a table), but you might also need to store the username to which that "last selected company" is tied.

Now, if each user utilizes their own copy of the front end, you could get by with just storing the value in a local table with no tie to the user which would be more accurately described as a computer based preference.  However, if your users never (or rarely) switch computers, the result from the user's perspective would be the same.
0
cbishopcgrAuthor Commented:
I will most likely keep the database on the server so that more than one can access it - but it will not really have to be a multi user sort of thing - more like one person would access it when the other one is out.

So if I just use the default at the one they would use mostly - when they open the form - and it is shown there as the default - then it will be recongnized as the one to use for that session, right?

They have a main company, and will eventually be using the other company - but they may not use all the functions under this for the second company - so mostly it will only be the default.

(you have been most helpful -so I want to make sure to award all the points to you)
0
AccessGuy1763Commented:
If you have a Default Value in place that will always be what shows up for that combo box when the form is opened unless you also add code like I describe above to remember the last selected Company within a database session.

I'm 99% sure that the code I suggested for Form_Load would overwrite the DefaultValue.  Of course, the first time the form is opened per database session lngGLFormLastSelectedCompanyID will be 0 so the form would fall back to your DefaultValue setting.
0
cbishopcgrAuthor Commented:
Ok - I believe that covers it all - you have been a great help - I will use your other suggestions in other projects - looks like the Defaul will really be the main thing I will need to allow me to finish this project.
0
cbishopcgrAuthor Commented:
Very helpful and offered a few different solutions that really helped me make my project work as I needed.  Thank you so much.
0
AccessGuy1763Commented:
Glad I could be of help... good luck!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.