468,296 Members | 1,269 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,296 developers. It's quick & easy.

Randomize

264 256MB
Hi,

I am trying to create a form, that will randomize data from a table. For example, I have a table called "tbl_master". It has 3 fields, sport, athlete, speed. I would like the form on a button press to take one random entry from each field. I have managed to do this with one field, but cannot figure out how to do it for all 3. What i have done so far is created a select query and use the following:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 tbl_master.sport
  2. FROM tbl_master
  3. ORDER BY Rnd(Len([sport])) DESC;
  4.  
That will randomize the sport field, but I am unsure how to make this work to randomize all fields. Thanks guys!
4 Weeks Ago #1
30 3983
NeoPa
32,071 Expert Mod 16PB
Interesting question.

I'm not sure this can be done very effectively using only SQL.

Personally I'd have a Form bound to the table but three Controls that call an internal Function to populate the values. The Form, being bound, would have Recordset and RecordsetClone properties, even though the current record would never be visible as such due to there being no bound Controls to show it.

The three Controls that are there (txtSport, txtAthlete & txtSpeed) would have their ControlSources set to =RandomVal('Sport'), =RandomVal('Athlete') & =RandomVal('Speed') respectively and the code for RandomVal() would be something like (Air code.) :
Expand|Select|Wrap|Line Numbers
  1. Private Function RandomVal(strField As String) As Variant
  2.     Static lngNumRecs As Long
  3.     Dim lngMove As Long
  4.  
  5.     With Me.RecordsetClone
  6.         If lngNumRecs = 0 Then
  7.             Call .MoveLast()
  8.             lngNumRecs = .RecordCount
  9.             Call Rnd(Number:=-lngNumRecs * Timer())
  10.         End If
  11.         Call .MoveFirst()
  12.         lngMove = CLng(Format(Rnd() * lngNumRecs + 0.5, "0")) - 1
  13.         If lngMove > 0 Then Call .Move(Rows:=lngMove)
  14.         RandomVal = .Fields(strField).Value
  15.     End With
  16. End Function
* Corrected poor usage of Rnd().
4 Weeks Ago #2
Rabbit
12,512 Expert Mod 8TB
Nothing's preventing you from selecting additional fields
4 Weeks Ago #3
cactusdata
164 Expert 128KB
You will have to use subqueries, for example:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 
  2.     ID,
  3.  
  4.     (SELECT TOP 1 Sport1
  5.     FROM tbl_master
  6.     ORDER BY Rnd(-Timer()*[ID])) As Value1,
  7.  
  8.     (SELECT TOP 1 Sport2
  9.     FROM tbl_master
  10.     ORDER BY Rnd(-Timer()*[ID])) As Value2,
  11.  
  12.     (SELECT TOP 1 Sport3
  13.     FROM tbl_master
  14.     ORDER BY Rnd(-Timer()*[ID])) As Value3
  15.  
  16. FROM tbl_master
Note please, the use of Timer() and an ID with Rnd. If not, you'll get the same result whenever you close Access, relaunch, and run the query.
4 Weeks Ago #4
didacticone
264 256MB
Neo and Cactus, I tried both of your suggestions and they both work. I'm going to test them out some more and see the pros and cons of each, but thanks so much for the help!
4 Weeks Ago #5
NeoPa
32,071 Expert Mod 16PB
There you have a version that will work for you in SQL too. Excellent.

My preference is still to use the function approach as that simply moves around within a single Recordset object and doesn't require the table to be processed through so much. There again, your original request used SQL as an example so may well be more what you're after. You have both now.

CactusData's post also reminded me to be more careful of my use of the Rnd() function. It does indeed require a negative number in order to seed it - although what can work pretty reliably is making a conscious choice NOT to seed it at all and simply continuing from whenever it was last used - on the basis that, in most cases at least, that is pretty random in itself.

However, my code has been amended to reflect seeding it pseudo-randomly the first time it's called in a session.

Rabbit:
Nothing's preventing you from selecting additional fields
Although I'm not absolutely clear on what you mean and what you're replying to I assume you're suggesting SELECTing multiple Fields from the same, randomly selected, Record.

I suspect that's not what the OP's after. My understanding of the request is that they're specifically looking for each Field value selected separately and randomly from within the distinct sets of data for that particular Field.
4 Weeks Ago #6
didacticone
264 256MB
Thanks for the follow-up neo, and your interpretation was 100% correct, I want each field value selected separately and randomly, and you stated. I have a follow up if you don't mind. With either method, would there be a way to just "requery" one of the fields and not the entire query. I am currently working using cactus' method and if I create a button with the me.requery it will randomize all of the fields. I am unsure as to how, if possible to have a button next to each field and randomize only that field from within the form?
4 Weeks Ago #7
cactusdata
164 Expert 128KB
You could save each of the subqueries above as a separate query, say, Q1, Q2, and Q3.
Then use DLookup in the ControlSource of the three textboxes, like:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[Sport1]","Q1")
  2. =DLookup("[Sport2]","Q2")
  3. =DLookup("[Sport3]","Q3")
Each textbox can now be requeried at will.
4 Weeks Ago #8
didacticone
264 256MB
That's what I was actually going to do lol, but then I thought, with more data that I add I would have a lot of queries and was just curious if there was a more condensed way to do it that anyone knew of. I'm by no means an advanced user and usually my solutions are work arounds until I learn a better way. Thank you for the feedback!
4 Weeks Ago #9
Rabbit
12,512 Expert Mod 8TB
Ah, my bad, I misunderstood the question
4 Weeks Ago #10
didacticone
264 256MB
Also, when I add a command button, and try to requery the text box, it is requiring all of the text boxes? I have the controlsource set as you outlined and the command button has:
Expand|Select|Wrap|Line Numbers
  1. me.sport1.requery
4 Weeks Ago #11
NeoPa
32,071 Expert Mod 16PB
You may want to rename the TextBox Controls such that they use different names from the associated Fields. That may help. It would ensure the compiler knows it's the Control you want to Requery rather than the Field.

As I said earlier, my personal preference would be to use the associated code module with the function but I can see you're more comfortable with using queries / SQL.

You'll notice that the suggestion in post #8 is similar to my approach in as much as it sets the ControlSource of each of the Controls to a Function reference. Once you take that step of course, your horizons are extended immensely. You can do almost anything you want using VBA. You can optimise the logic and be less tied to just what's available via SQL or queries directly.
4 Weeks Ago #12
didacticone
264 256MB
I tried renaming and it is still doing the same thing, I don't get it. And you are correct I am definitely more comfortable with this route, but my plan was to get it to function and then try it the way you recommended as to learn it a little better. Part of my anticipated issue is, won't I run into the same requery issue doing it your way as well?
4 Weeks Ago #13
NeoPa
32,071 Expert Mod 16PB
DidacticOne:
Part of my anticipated issue is, won't I run into the same requery issue doing it your way as well?
I would certainly expect so - yes. Essentially both end up calling a function so what's done in the function is almost irrelevant.

Frankly though, the behaviour you describe is unexpected and doesn't seem to fit with the idea of Requerying an individual TextBox Control - and that certainly is supported as a separate item from Requerying a Form.

In the cirumstances (of not really understanding what is behaving in a way that's unexpected.) it may be worth trying and seeing what you get. After all - what's Me.Sport1 when it's at home? It's not in your original question so harder to guess exactly what you're doing. Just to be very clear - you should be running the Requery() Method of the TextBox Control - not of the Form it's on.
4 Weeks Ago #14
didacticone
264 256MB
Hey Neo, so I created the form using your method, and it is still requerying the entire form. I don't understand what you mean when you ask "what is Me.Sport1 when it's at home". Also, maybe I am coding it incorrectly, I am creating a command button, and in the on click event I am putting the code above...
Expand|Select|Wrap|Line Numbers
  1. me.sport1.requery
or the different names I have tried.
4 Weeks Ago #15
NeoPa
32,071 Expert Mod 16PB
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('Sport') 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.
4 Weeks Ago #16
didacticone
264 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. Me.sport1 = RandomVal(Sport)
but i receive an error "item not found in this collection"
4 Weeks Ago #17
NeoPa
32,071 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Me.sport1 = RandomVal('Sport')
you may find that it works as required.
4 Weeks Ago #18
NeoPa
32,071 Expert Mod 16PB
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.
4 Weeks Ago #19
didacticone
264 256MB
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
Expand|Select|Wrap|Line Numbers
  1. =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
Expand|Select|Wrap|Line Numbers
  1. 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.
4 Weeks Ago #20
didacticone
264 256MB
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
Expand|Select|Wrap|Line Numbers
  1. me.txt_sport1.value=DLookup("[Sport1]","Q1")
  2. me.txt_sport2.value=DLookup("[Sport2]","Q2")
  3. me.txt_sport3.value=DLookup("[Sport3]","Q3")
Then I also have a button next to each text box with the following:
Expand|Select|Wrap|Line Numbers
  1. 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!
4 Weeks Ago #21
NeoPa
32,071 Expert Mod 16PB
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.
4 Weeks Ago #22
didacticone
264 256MB
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.
4 Weeks Ago #23
cactusdata
164 Expert 128KB
But then you need nearly no form code. Just use these as the ControlSource for the three textboxes:

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[Sport1]","Q1")
  2. =DLookup("[Sport2]","Q2")
  3. =DLookup("[Sport3]","Q3")
and the button click event is reduced to:

Expand|Select|Wrap|Line Numbers
  1. Me!txt_sport1.Requery
4 Weeks Ago #24
didacticone
264 256MB
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.
4 Weeks Ago #25
NeoPa
32,071 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open()
  2.     With Me
  3.         .txtSport = RandomVal("sport")
  4.         .txtAthlete = RandomVal("athlete")
  5.         .txtSpeed = RandomVal("speed")
  6.     End With
  7. End Sub
  8.  
  9. Private Sub cmdSportRequery()
  10.     Me.txtSport = RandomVal("sport")
  11. End Sub
  12.  
  13. Private Sub cmdAthleteRequery()
  14.     Me.txtAthlete = RandomVal("athlete")
  15. End Sub
  16.  
  17. Private Sub cmdSpeedRequery()
  18.     Me.txtSpeed = RandomVal("speed")
  19. 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 :
Expand|Select|Wrap|Line Numbers
  1. ?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.
4 Weeks Ago #26
NeoPa
32,071 Expert Mod 16PB
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.
4 Weeks Ago #27
NeoPa
32,071 Expert Mod 16PB
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.
4 Weeks Ago #28
didacticone
264 256MB
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!
3 Weeks Ago #29
NeoPa
32,071 Expert Mod 16PB
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.
3 Weeks Ago #30
didacticone
264 256MB
Appreciate the explanation, good advice, thank you, and thanks to everyone else on this thread for their input. The database is starting to come together nicely and I truly appreciate all of the help provided.
3 Weeks Ago #31

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Fieldmedic | last post: by
3 posts views Thread by Gaffer | last post: by
1 post views Thread by Ellen Manning | last post: by
4 posts views Thread by Mr. x | last post: by
1 post views Thread by Badass Scotsman | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.