Textbox Data in Form with record source based on query

[accessforumck]

I have a textbox in a form. The form has record source based on query. The textbox has a control source of a tied to a field.



Is the data shown on the textbox pulled from the table where the field is or does the textbox pull data based on the result of the query?

[ranman256]

the text box pull from the query,
the query pulls from the field.

so its the same thing, the text box pulls from the table (via query)
the query is customizable. the table is not.

[accessforumck]

Hi ranman256,

Thank you for your reply. I also had the same understanding as you explained. However, my understanding was challenged when I encounter a situation. Please see the attached file. I try to illustrate my doubt. It seems like the textbox only gets its data from the table and not the query.

[aytee111]

The thing is that on this forum the people who answer your questions are techy folks and don't do some of the things - definitely we do not do lookups on tables, so we cannot answer those questions easily. Lookups on tables are for very simple users with simple databases, when you add any kind of sophistication all that happens is what you have here, very strange and unanswerable things!

[accessforumck]

Hi aytee111,
do you refer to the lookup field setup in the table, tbl_product? I had removed that but the form still behaves the same. Please see attached database.

[aytee111]

What you are doing when you select from the combobox is to change the data on the table. Usually a combobox is used for selecting a value and then certain things would happen after that. But you have it bound to the table, so as you change the PK so the type is being changed too. If you watch the data on the table you can see it changing each time you change the combobox selection.

[accessforumck]

Hi aytee111,
I tried your proposal.
I set the form property, pop up, to yes and have the tbl_product_type table in the background. However, when I make a selection in the combo box, there is no change in the selection in tbl_product_type table.

[aytee111]

The reason this is happening is that the combobox is bound to the table, so whenever you make a selection in the combobox the data in the underlying table changes. That is why the textbox value changes to keep up with the selection. The combobox must be unbound, its control source must be blank.

You need not have changed the form type nor the record source, the problem is with the combobox only.

[accessforumck]

Hi aytee111,
I appreciate your prompt replies.
I understood and can see that the textbox value changes to keep up with the selection in the combo box.
I do not understand the reasoning behind this behaviour because the textbox is bounded to the form's record source query and should not be the table, tbl_product_type. Why does it reflect the data from the table? Shouldn't it only reflect data based on the form's record source query?

Sorry I could not really apprehend this behaviour.

[aytee111]

When you open the form there are no records to show, your query has returned no results. So when you click on the combobox it adds a record to the table, the query now shows one record with both fields filled in. Does this make sense?

A query is only showing what is on the table, it is not a source of data by itself. I took the record source and made it a query, then had the form open and the query open at the same time. It makes a lot more sense to see them side-by-side to see what is going on.

[accessforumck]

Hi aytee111,

I totally agreed with you on the statement, "So when you click on the combobox it adds a record to the table, the query now shows one record with both fields filled in". I always believe it should behave like this.

I tried to open the form and query side by side but I do NOT see what you had explained. Please see the steps I had taken in the attached file.

Thank you for taking a look at my file and helping. Appreciate your help a lot.

[aytee111]

You have got me running in circles now! The answer to your first post is neither it seems, it must be happening in cache or memory or whatever Access uses. I replicated this in version 2010 as well as 2013 and there is no difference in behavior. I guess I don't know Access well enough to know what is happening behind the scenes. Sorry I haven't been able to answer your question, you may want to start a new thread and get one the real experts to answer!

[ssanfu]

I've been trying to follow this thread, but I am really confused.

Looking at your pdf, the reason the results are different (IMO) is that there is a record where "tbl_product.product_type_id" (which is the FK) does not have a value that is in the related PK field (ie the value is NULL).

You execute a query and the record is displayed.
You open the form, the combo box cannot display a record where the two fields (PK/FK) do not match.
Copy the form and change the combo box back to a text box (on the form) and all records are displayed.


@accessforumck, would you please explain what you are trying to accomplish. Not in detail, not HOW you think it should be done, just the overall process.
Pretend we are in line at Burger King and having a discussion about the problem.
Then we can get into the how of it. Or maybe you are just trying to get an understanding on what is happening.....

[accessforumck]

Hi Steve,

Thank you for coming in to help. I am trying to understand on what is happening.

The behaviour of the textbox returning a result based on the selection of the combo box is NOT CORRECT in my opinion. This is because the textbox's control source was bounded to the form query. In the pdf file that I had attached, I try to show that there is no result from the query. However, strangely, the textbox shows a result. Do I make sense?

[ssanfu]

Your set up is, well, different.

You don't have a "Query1" in your example dBs, so I used the look up field to create a query (Query1).
Then I created another query to show all of the records in tbl_products. (Query2)


I created two forms to demonstrate the records returned from the two queries.


Normally I would expect to see a main form with a record source a query based on tbl_product_type and
a sub form with a record source a query based on tbl_product.

Or a form with a query based on on tbl_product. A combo box would have the row source of a query based on tbl_product_type (product_typeID_PK and product_type).


Again, would you please explain what you are trying to accomplish. Not in detail, not HOW you think it should be done, just the overall process. Or is this a learning experience??