Results in text box in form footer won't show

[latex88]

I have a continuous form that is bound to a table. I have a button in the records, so that when a record is selected, a text box (txtAddressSelected) at the footer becomes visible and is filled with the data from the record selected. The control source of the text box is a function, which is tied to a global variable that is set equal to the aforementioned record when selected. That process works fine.

However, when there is no record upon the form opens, I have a text box (txtNewAddress) in the footer of the same form that allows the user to enter new data, and upon pushing a button, the same text box (txtAddressSelected) is supposed to show the new data filled. In the code I have it requery (me.txtAddressSelected.requery). When I run it, I see it doing its thing, I see the textbox flashing real quick, but the box remains empty. I have checked the function and the global variable. They both show up in the Immediate window, so I know the data is passed correctly. I have been debugging it for hours, but I just can't get txtAddressSelected to show the new data entered. Any suggestions? I'm sure it's something stupid, but I'm game for humiliation at this point.

 

[AccessMSSQL]

When are you setting your global variable?

 

[latex88]

When are you setting your global variable?

In the module section, not within the form. If I call this function somewhere else, it will be available.

 

[AccessMSSQL]

I believe the issue is that your txtAddressSelected has a control source that is your function looking for the address in the currently selected record. You can't set txtAddressSelected to any other value because the function will continue to run and look for the value in the currently selected record. When user types something in txtNewAddress and clicks the button, you refresh txtAddressSelected, but function runs again, finding no record.

Do a test, make the txtAddressSelected unbound (remove the function from the control source - for now), then do a test with txtNewAddress. See if value stays in txtAddressSelected. If so, the issue is the function.

 

[latex88]

If I understand you correctly, I believe what you're saying is true if we are referring to the same button. There are actually two different buttons. One is next to the records if the form returns result from a query. The other button is next to txtNewAddress, which is a textbox that allow the user to enter a new address if none is found.

When I first read your response, it didn't make sense, thinking that you were referring to the same button, so I did remove the function from txtNewAddress and tested. As suspected, nothing happened, meaning txtNewAddress is still blank, so I proceed to close the form without saving. Upon reopening it, I do see the textbox filled with what I previously filled. That's never happened before. So, I'm thinking, ok, I guess the only way to requery is to close and open the form again. It would not have been the most optimal choice, but hey. Then I entered some more texts to test, voila, txtAddressSelected is now populated.

I don't understand what changed. The only thing I did was deleting the function from txtAddressSelected, closed the form without saving, then opened it up again. The function is still there upon inspecting. That's weird. I must have done something elsewhere to affect this. It's working now, but I don't understand it at all. If I figure it out later, I'll come back and post. Perhaps this is an Access bug of some sort?

 

[AccessMSSQL]

You know what, you probably had both text boxes (txtNewAddress and txtAddressSelected) bound to the same function. I bet that's what the issue was. So you say the address boxes flickered, if they both had the same function than when you requeried txtAddressSelected you ended up kicking off an endless loop of that function running.

 

[latex88]

No, txtNewAddress was an unbound text box the entire time. It is meant for user to enter a new address if no matching records where found from a telephone number entered in another from. I think something similar may have happened to me before, but it was due to the label not being visible. For troubleshooting, I made txtAddressSelected and the labels visible without being prompted, so, I'm still clueless to why it's working now. I do appreciate your help. For some reason, deleting that function off temporarily did the trick...I guess

 

[latex88]

OK. My celebration was short-lived. It's not working again, and I think I may discover the problem, but I do not have the solution for it.

txtSelectedAddress will requery only when there's an record shown in the form, regardless if I select that record. When there's no record, it cannot be requeried. Let me elaborate the whole form again.

The form (frmAddress) is basically based on a query whose criterion is from a telephone number entered in another form. If there's a matching telephone number, this form will display one or more addresses tied to that telephone number. The user then can select one of the address(s). Once selected, txtSelectedAddress is supposed to show what the user has selected before moving on. When an address is selected, the value of the textbox is set to equal to strAddressSelected (Global variable). (See below)
Now, if there is no result from the telephone number entered, then the user can type in a new address in the same form (frmAddress - that’s where the txtNewAddress reside, in the footer). Once the user has filled the text box, he/she must click another button (btn1) before another button becomes visible to proceed. Upon pressing btn1, strAddressSelected = txtNewAddress and the txtAddressSelected is requeried, and there’s where the breakdown occurs. txtAddressSelected (also located in the footer of the form) is just blank. When I test strAddressSelected and AddressSelectedFunction() in the immediate window, I do see the value entered in txtNewAddress.
More detail
The control source of textbox, txtAddressSelected is “=AddressSelectedFunction()”
The code in global module (I’m not sure the proper term is called) is below

Public strAddressSelected as String
Public Function AddressSelectedFunction()
AddressSelectedFunction = strAddressSelected
End Function

 

[AccessMSSQL]

Is it possible for you to post your project? It seems like you have too many steps to accomplish what you are trying to do. I don't really think you need the global function.

 

[latex88]

It's too big. I would have to strip it down to post, but I abandoned sending the selected address to another text field. I ended up changing the color of the text box of the record selected, so that user knows that their address has been selected.

 

[AccessMSSQL]

Okay...good luck with the rest of your project!