Bound database fields (1 Viewer)

seb1972

New member
Local time
Today, 09:08
Joined
Mar 23, 2021
Messages
3
I am a sql programmer but am trying to figure out an access form (.accdb). There are several forms (tabs) and I want to know how the data from the SQL database is being displayed. Do I look at design view or layout view? Is there some link in the properties sheet that I am missing? I have looked in the navigation, on and all over the form and for the life of me I can not find any useful information. I think some of the data is in stored procedures but I would like to physically see how it is working.

Thanks for the help!
SEB
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:08
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

If you view the Form in Design View, what do you see in the Record Source property?
 

plog

Banishment Pending
Local time
Today, 09:08
Joined
May 11, 2011
Messages
11,611
Brief Access overview and glossary--In the Access world, forms and tabs are unique entities. Forms are one of 6 Access objects (tables, queries, forms, reports, macros, modules); a tab can be part of a form. You would add a tab control to a form and then could have as many tabs on that tab control as you wanted. Further, tabs often contain sub-forms. Those subforms are Form objects themselves, just placed on another form. Every form can have its own Record Source (or none at all--unbound). So, when you open a form the form itself can have a Record Source and then each subform on it can have its own Record Source.

Now, to view the datasource of a form--open it in design view, right click anywhere on the form, then click on Form Properties in the pop up menu and go to the Data tab in the Property Sheet menu that appears. The topmost value is Record Source and it list what data is feeding the form. That can be the name of a table or query or the SQL of a query.
 

seb1972

New member
Local time
Today, 09:08
Joined
Mar 23, 2021
Messages
3
Thanks for the reply. Record Source on the data tab is null. This form does have tabs...

I think I figured out how the data gets loaded. But now I have a text box that has data in it, how can I tell what sql column is bound to that text box? There is nothing I see in the properties of the text box.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 19, 2002
Messages
42,970
Good luck. It sounds like you are working with an unbound form. You are going to need to learn VBA to read it. If you can post the .accdb, and tell us the name of the form, someone can decode it for you. Obviously we wont have the data but that doesn't matter for this question.
 

plog

Banishment Pending
Local time
Today, 09:08
Joined
May 11, 2011
Messages
11,611
In Design View of the Form, right click on the text box and click on Properties on the menu that pops up. Then in the Property Sheet go to the Data tab and at the top is Control Source. That tells you what field is bound to the text box.

This can hold a variety of things--the name of a field from the Forms Record Source, a call to a function or nothing. If nothing then its an unbound form and some VBA somewhere loads the data. In that case you click on the View Code option in the Ribbon and search for the name of the textbox.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:08
Joined
Feb 28, 2001
Messages
26,999
OK, let's talk "mechanisms." On an access form, there are two major ways that controls on the form (text boxes, combo boxes, check boxes, etc.) get populated. The two methods are radically different. (There are also a few minor ways, but I'll avoid them at the moment.)

#1 - BOUND forms - you name a .RecordSource for the form, which can be a table or query that "exposes" multiple data elements (i.e. fields). Then for each bound control, you name a .ControlSource that names a field associated with the .RecordSource. Access has these "events" in which certain highly specific things happen at certain times. The event relevant to this discussion is the Form_Current event, in which Access (behind the scenes) visits each control on the form. For each control that is bound, it plucks a copy of the value from the field in the .RecordSource that was defined by the individual .ControlSource - and at the end of that once-through, all controls that were GOING to be loaded HAVE BEEN loaded. If needed, the user could have also entered a link to a Form_Current event routine in which some VBA code might load controls that were omitted during the Access scan for BOUND controls. Between Access automatic control population and a possible Form_Current routine, that is how you normally populate data into forms.

#2 - UNBOUND forms - much trickier - because certain events (Current being one of them) don't occur for unbound forms. Therefore, someone has to write VBA code to communicate with the intended data source to extract and load values to controls. Your form MIGHT have some buttons that you can click to drive it and trigger it, or it might be more fully automated. Though with an SQL backend, I think it has to stay synchronized with some data cursor so what it does will be more "episodic" i.e. dependent on something the form's user does.

Sounds like what you have is case #2, which means if you want to find out how something gets loaded, you have to open the form in design mode, then in the ribbon switch to database tools, then open the code / macros page. Somewhere in that part of the DB, you will find the code that loads stuff. I'm GUESSING that the code in question will deal with some kind of recordset, in which case the starting point for that sequence is whatever code segment executes a .OpenRecordset function, which will name an SQL-resident table.

Here is the kicker: By having unbound forms, you are automatically starting at an extreme disadvantage, because MOST of the time, Access will do exactly what you would want it to do through BOUND forms. Therefore, whoever set this up was either (a) ignorant of how Access works with SQL or (b) is well aware that Access doesn't do what the original author wanted. So in either case, you are dealing with something very complex.
 

seb1972

New member
Local time
Today, 09:08
Joined
Mar 23, 2021
Messages
3
Thanks. That help a bunch.

Can you explain what this line is going in Visual Basic:
strCustID = BFValue(rstExp!Customer, 3)
 

plog

Banishment Pending
Local time
Today, 09:08
Joined
May 11, 2011
Messages
11,611
It's calling a custom function--BFValue(). It's passed the Customer value from the recordset its using and the number 3 for some reason. Judging by the variable name (strCustID) the function is returning the ID value of that customer.

Open the code editor and search for "BFValue" and you'll eventually find the full function code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 19, 2002
Messages
42,970
Or, a shortcut to find where something is defined - right click on the variable and choose Defined.
 

Users who are viewing this thread

Top Bottom