Randomize

[NeoPa]

NeoPa:
What is Me.Sport1 when it's at home

Good question - and a lesson to me not to use colloquial terms when communicating on an international forum site :-(

What it means is to tell me about Me.Sport1 in more detail. So far we know that it's something on your Form but we don't have anything specific. It matches CactusData's suggestion in posts #4 & #8 but not your original question so I'm looking for you to confirm that it's actually a TextBox Control that you're using on your Form to represent and display data from the [tbl_master].[sport] field described originally.

At this point I recommend that you look through the Help topic for Requerying of Controls. It's quite involved and shows when the Control itself is requeried and when it will Requery the whole Form. From this it would seem that, contrary to my earlier statement, while a .Requery of the Control for CactusData's scenario SHOULD work on just the Control, one for my scenario wouldn't.

It may be that my approach wouldn't allow such re-querying of an individual Control. If that's required though, you could populate the values in the Controls using code instead. IE. Instead of using =RandomVal('Spo rt') etc, leave the ControlSources blank instead and simply set their values when the Form is opened and again whenever you want to trigger a change.

[didacticone]

All good my friend, i thought that was what you meant but just wanted to make sure. The "sport1" is a field in a table called tbl_master, it is a short text field.

I looked through the link you provided and tried a couple of different scenarios using cactus' method and it is still just requerying the entire form.

Your third paragraph slightly confuses me. I removed the controlsource and created a command button using:

Me.sport1 = RandomVal(Sport)

but i receive an error "item not found in this collection"

[NeoPa]

DidacticOne:
The "sport1" is a field in a table called tbl_master

Are you sure? That contradicts what you told us earlier - which is that the three Fields in the Table [tbl_master] are called [Sport], [Athlete] & [speed]. You certainly didn't mention one called [sport1] & I find it hard to believe that's another Field in the same Table going by the conversation so far.

I could believe it's a Contol (probably TextBox) on your Form that's used to display a value from the [sport] Field.

DidacticOne:
Your third paragraph slightly confuses me. ...

You did exactly what I was suggesting except that you failed to copy the ControlSource across accurately. If you try instead :

Me.sport1 = RandomVal('Sport')

you may find that it works as required.

[NeoPa]

DidacticOne:
I looked through the link you provided and tried a couple of different scenarios using cactus' method and it is still just requerying the entire form.

If that's the case then I'm surprised. You can probably guess I have no personal experience of requerying Controls myself. I'm just going by the Help information I reviewed - just as you did.

What I read there led me to believe that, as CactusData's suggestion included a Domain Aggregate function call that it would be requeried at the Control level as is the actual requirement. Generally the Help's pretty accurate but I can remember situations where it is actually wrong in its detail. Maybe that's something that should work, logically, but where Access doesn't behave exactly as advertised so it doesn't. In situations such as this you have to go by trial-and-error unless you're lucky enough to find something somewhere by someone who's done that already and can guide based on their own personal experience.

[didacticone]

Hey Neo, so my apologies, I am not using the real field names as I do not believe I have the rights to post them (my job may not want that). I can assure you that every field in my form is link to a field in the underlying table. I only have one table in the database as well. I was confused by "sport" and just used cactus' example of "sport1", again my apologies for the confusion. To clear it up, using your method, I have a form that the recordsource is set to my table. I have three unbound text boxes with

=RandomVal('Sport')

in the controlsource, obviously I have the field names from the table after "RandomVal" .

As for the code you suggested, I did actually try it that way first but I get an error "Expected Expression"... thats when using

Me.Press = RandomVal('Sport')

I tried a few different variations and can't seem to get it to function properly.

And I agree with that article you sent, I have actually read it prior to coming to this forum, it seems what I am trying to do isn't all that common so its been difficult trying to find examples, but I do appreciate your help and feel like we are getting close.

[didacticone]

So I think I came up with a solution that seems to work. Curious as to your thoughts. I have made the text boxes unbound. The forms recordsource is a basic select query I made to filter out null fields as that was causing an error. In the on load event of the form I have

me.txt_sport1.value=DLookup("[Sport1]","Q1")
me.txt_sport2.value=DLookup("[Sport2]","Q2")
me.txt_sport3.value=DLookup("[Sport3]","Q3")

Then I also have a button next to each text box with the following:

me.txt_sport1.value=DLookup("[Sport1]","Q1")

This seems to working as I would like it to... again just wanted to post to see your thoughts... thanks!

[NeoPa]

Hi again.

It seems there is some confusion over the two terms Field & Control. Forms don't have Fields on them as such. They have Controls typically. The Recordset of a Form may have Fields so it's very important to use the terms appropriately - especially when wandering into less-travelled territory. Bound Controls also have a Field of course.

This is important because when you refer to Fields when you really mean Controls it's :

1. A little harder for me to understand what you mean. Not a great deal as it's usually quite obvious.
2. Very hard for me to explain things back to you as you won't understand what I'm saying accurately as long as you confuse the two terms.

It may be worth re-reading some of the existing replies with that understood.

Another point to deal with to save us introducing further confusion is that you have no obligation whatsoever to use names that your client may not wish you to share. However, and this is a very BIG however, it's critical that you use names consistently. You have three Field names in your OP. They are fine. Everything else is a level of confusion that makes life hard both for yourself and anyone who tries to help you. Now you have a better understanding of the difference between a Control & a Field, it would also be helpful to know the names of the Controls associated with each Field - regardless of whether they are actually bound or not.

Now, to be clear, I don't want you to see this as a whinge and a moan or any sort of indication that I/we are unhappy with your posting. It is important though, that you understand these finer points. It's clear that you're generally quite careful and well-mannered. That's great and we all appreciate that. I suspect these are simply points you weren't aware of.

I'll post separately in response to the technical state of the thread.

[didacticone]

Hey Neo,

I try to make things as clear as I can, because I know that is the only way you can provide substantial assistance; however I am not formally trained and most of my learning has been through books and the internet, so there are times, especially when I am rushing to get a response back on a message board and I am not articulating terms correctly due to haste or lack of knowledge.

I understand forms do not have fields per se, usually I phrase it this way to imply that it is a control bound to a field on a table/query. I will typically keep the names of my controls on a field rather consistent, e.g. if the field name is "WATER" I will make the control name "txt_WATER" if it is a text box. Again, something you could not possibly know, so I should have been clearer. Your post has taught me to be more careful with making the assumption that people will infer my information. So I thank you for that.

Also, I totally understand your point. Perhaps I should have clarified the fact that I was not using the exact names at the start; working on the database and then trying to ensure I am using the same "names" in this post was confusing me, so that's on me, no excuses there. I understand that just makes everything more confusing for everyone.

And no hard feelings at all, I appreciate the insight and yes, I am trying to be thorough, yet concise in my posts, as to not waste the time of people such as yourself, who are just trying to help me. I look forward to what you think of what I worked on. Thanks again.

[cactusdata]

But then you need nearly no form code. Just use these as the ControlSource for the three textboxes:

=DLookup("[Sport1]","Q1")
=DLookup("[Sport2]","Q2")
=DLookup("[Sport3]","Q3")

and the button click event is reduced to:

Me!txt_sport1.Requery

[didacticone]

That's what I tried initially cactus, but when I would try to requery only the control, it would requery the entire form. I tried my way after reading online that if the controlsource is set to dlookup, it will requery everything, not sure if that is true as a rule or not, but I know thats what was happening to me.

[NeoPa]

Hi again.

At some stage you'll update us with the actual (By which I mean as translated for use here.) names of your Controls that are used to show data from your three Fields [sport], [athlete] & [speed]. Until then though, I'll use (respectively) [txtSport], [txtAthlete] & [txtSpeed].

To reiterate more fully what I said previously, what you'd be looking at, and this would go for Cactus' approach too as it seems clear now that a Control.Requery is not requerying the Control only, as was expected, is setting the Values of the separate (unbound) Controls when the Form is first opened and then setting them again individually whenever the associated CommandButton is clicked.

That would look something like :

Private Sub Form_Open()
    With Me
        .txtSport = RandomVal("sport")
        .txtAthlete = RandomVal("athlete")
        .txtSpeed = RandomVal("speed")
    End With
End Sub

Private Sub cmdSportRequery()
    Me.txtSport = RandomVal("sport")
End Sub

Private Sub cmdAthleteRequery()
    Me.txtAthlete = RandomVal("athlete")
End Sub

Private Sub cmdSpeedRequery()
    Me.txtSpeed = RandomVal("speed")
End Sub

You explained earlier that you got an "Expected Expression..." error when you tried it before. If that's the case then the next step is not to try a different approach but to find out why and resolve the issue. If every time you hit a snag you simply look for a way around it then you'll get nowhere in programming. It's not an approach I could recommend in any part of life really, but certainly won't lead to success in programming.

My first suggestion would be to compile the code. Never work with uncompiled code as explained in Before Posting (VBA or SQL) Code.

If that's all up-to-speed then go to the Immediate Pane when the Form is open and execute :

?RandomVal("speed");

If that shows a value then the rest of the code should work. If not then that is where you need to focus in order to proceed practically.

CactusData:
But then you need nearly no form code. Just use these as the ControlSource for the three textboxes:

As explained earlier (Post #11) that proved not to work in their test. As discussed since this seems contrary to what the Help system says, which supports the idea that it should work. Nevertheless if it isn't working then we have to move on. If you can show otherwise we may need to look more closely at the OP's attempt to get it working in more detail.

[NeoPa]

DidacticOne:
however I am not formally trained and most of my learning has been through books and the internet, so there are times, especially when I am rushing to get a response back on a message board and I am not articulating terms correctly due to haste or lack of knowledge.

I believe more and more people are getting into coding in all sorts of areas without formal training nowadays. I would only say to learn where you can. Consider your training your own responsibility. Others can and will help, but the responsibility lies firmly in your own hands. I suspect you have the aptitude & understanding to do well. Over to you to make that happen.

Forms with Controls & Fields.
Most discussions really don't matter whether or not Control or Field is used. Those of us that offer help easily understand that in most cases one means the other. There are cases however, and this os one such, where the precise meaning is important. Now you understand the difference it makes sense for you to use the correct term when referring to either. Naming of your Controls is fine. They :

1. Are different names from those of the Fields.
2. Have an indication of the ControlType (TextBox, ComboBox, etc).

Names.
As long as there's consistency what you call them doesn't matter much. Control & Field names being clearly differentiable, as you have with yours, is great.

DidacticOne:
I am trying to be thorough, yet concise in my posts, as to not waste the time of people such as yourself, who are just trying to help me.

It comes across too. Having done this for a while, as others here have too, it's not hard to notice when a member is making an effort to be clear. My comments should not be seen as criticism but more as gentle guidance as to where to concentrate your efforts to produce the effects you're clearly aiming for.

[NeoPa]

NeoPa:
As explained earlier (Post #11) that proved not to work in their test. As discussed since this seems contrary to what the Help system says, which supports the idea that it should work. Nevertheless if it isn't working then we have to move on. If you can show otherwise we may need to look more closely at the OP's attempt to get it working in more detail.

Just to add to this, if we can show that this works as described in the Help system then it's a viable way forward.

On the other hand, if your tests consistently show that each time any is Requeried then all are changed instead of just the specified Control, then I see no way around that. We have no control over how Access / Jet / ACE works.

I should add though, that once you decide to take out the expression from the ControlSource of the Control and simply assign it values in code, then the same approach that works (Illustrated in Post #26.) should work just as well for calling DLookup(). Obviously using [sport], [athlete] & [speed] in place of those in the example.

[didacticone]

Hi, sorry for the absence, took a little break, anyways, just to be clear Neo, nothing you said was offensive, it was helpful and I totally understand your point, and it is another learning experience, so thank you.

I did have a question, in post #21 I gave my explanation of what I did and that it worked. Can you elaborate as to why the code you posted in post #26 is better (I am not doubting it, just want to understand how it makes a difference, as to not mess something up long term).

Thanks!

[NeoPa]

Good question.

1. Simplest is that the names are clearer as they better reflect the items you described in your question but that has no real technical relevance.
2. I prefer to avoid Domain Aggregate functions when I can. Essentially the concept here is extremely similar - the only differences are that your code uses the OnLoad event and mine uses the OnOpen one, and that yours uses a DA function whereas mine uses code that works on an existing Recordset that is already guaranteed to be open & ready.

In this scenario we're talking about differences in performance and logic that could be a hundred times bigger and still not be noticed, however as I'm aware a DA function has to go to the trouble of opening a recordset, processing through the full set of data (At least each record - not all data always needs to be loaded.) & also closing it again then I'm of the view that this is more complicated & involved than simply moving along a limited number of records within an already open dataset. Very theoretical I know, but to my way of thinking it makes sense.

OnLoad or OnOpen don't seem important. Whatever works for you. DA function or mine is equally almost negligible as I say. In the end that leaves you with whatever you're happier with. The concept is the same in both cases. Now you have the sample code for my suggestion at least though.

So, don't treat this as advice to use this approach over another. Simply as an illustration of the idea I originally proposed but updated to incorporate the extra understanding learned during the progress of this thread.