set the value of multiple fields at the same time

[super_paulie]

Hello guys.


Im pretty new to access but i have a question, i hope it is not misplaced.

I would like to set the value of multiple fields at the same time without having to reference all the fields. For example on a button click set the value of text1, text2 and text3 all to the number 5.

But i dont want to have to code:

on click
"text1 = 5"
"text2 = 5"
"text3 = 5"


i am looking for rather

"onClick text1,text2,text3 = 5"

i hope this makes sense and im sure its incredibly simple. I have looked around the internet but most guides are a bit over my head at the moment, can anyone shed any light on the above?

Thanks!
Paul

[andy49]

What's wrong with the4 line approach Paul.

[orange]

Paulie,
Welcome to the forum.

Please tell us more about your proposed database.
What are these "fields" and why are they named so generically (text1,text2, text3)?
These names don't convey any "business meaning"???

[super_paulie]

hi guys, thanks for the welcome.
Line 4 is the simpler method i am looking for! but what is the correct syntax?

Orange, hello!

The above example isnt actually part of my database but rather an example i invented to try to get my head around the syntax for such a procedure.

In reality, the fields will be true or false based on a value from another field. So in non-code speak i am looking to maybe have :

"afterUpdate check1,check2,check3= false"

thanks guys!

[orange]

Here's an example from an older database that shows how to process control names with sequentially numbered controls. The part in blue may be helpful to you. It tests each control Check1...Check4.

Private Sub btnAddLikes_Click()
10  On Error GoTo Err_btnAddLikes_Click
    Dim i As Integer
    Dim sql As String

20  For i = 1 To 4
30      If Me.Controls("Check" & i) = -1 Then
40          sql = "INSERT INTO tblUserHasQuality (userid,qualityId) Values( " & Me.UserId & "," & i & ")"
            'Debug.Print i & " " & sql
50          CurrentDb.Execute sql, dbFailOnError
60      End If
70  Next i
Exit_btnAddLikes_Click:
80  Exit Sub

Err_btnAddLikes_Click:
90  MsgBox Err.Description
100 Resume Exit_btnAddLikes_Click

End Sub

[Micron]

neither of the syntax examples you posted are possible. The closest you could get to would be Text11 = "11": Text13 = "13"...
Honestly, what you're really after is still kind of vague so we're kind of throwing out ideas. The code sample doesn't address the value of any other control, but perhaps you will be able to incorporate a test for that by yourself. Also possible that you could make use of the Tag property for the controls you want to effect.

[super_paulie]

thanks guys! sorry for being so vague, this is something that i am not up to yet so im thinking ahead in my free time.
Please see the attached screenshot to see if i can explain it better.

The combo boxes get their values from 2 separate tables and each box passes a numerical value 1 to 5. This value is multiplied by each other which gives us the values from 1*1 (1) up to 5*5(25).

Based on these 2 values the check-boxes over on the grid (which is just an image background) are assigned to be either true or false to indicate. Only one checkbox will be true, the other 24 will be false and then the checks that ARE false are set to hidden. This value is stored and used elsewhere and the checkbox fields are named 11,21,31,41 etc.
This runs off the "after_update" event on both checkboxes as well as the onLoad.

The box under "total" with potential value from 2 (1*1) all the way up to 25 (5*5) which is purely a visual cue, this doesn't pass anywhere as say combo1 is 4 and combo2 is 2 equals 8, whereas combo1 is 2 and combo2 is 4 which also equals 8, but is in a different position on the grid, so this is just a visual reference total.

I hope this makes sense guys, sorry if i am not explaining it very well i am just starting out here.

Cheers!

[isladogs]

Rename your checkboxes chk1 through to chk25 for simplicity.
Hide all of them by default

Set an integer variable e.g N equal to the product in your calculation

Then use code like this

me("chk"&N).visible=true

[Gicu]

I would write a custom function to deal with this:

Private Function DealWithCheckboxes(N as integer)
dim i as integer

For i=1 to 25
  if i<N then 
      me.controls("chk" & i)=true
  else
     me.controls("chk" & i)=false
  end if
next i


End function

Now you can call this from a button or even better from the AfterUpdate events of the two combo boxes. You will have to check if they are both populated - If Not Isnull(Me.combo1) And Not IsNull(Me.combo2) - and if yes you call DealWithCheckboxes(Me.combo1*me.combo2).


Cheers,
Vlad

[isladogs]

I think you meant

if I<>N Then

[Gicu]

Thanks Colin, I did have a mistake, I actually meant i<=N. I believe Paul's intent is to turn on (true) the N number of check-boxes, leaving the rest off (false). So if one combo has the number 5 and the other the number 2 he wants to have 10 checkboxes checked on 15 unchecked. Is that what you're after Paul?

Cheers,
Vlad

[isladogs]

Hi Vlad

If you look at post 7, the OP wrote:

Only one checkbox will be true, the other 24 will be false and then the checks that ARE false are set to hidden.

So if I was to use your function, I would change it to:

Private Function DealWithCheckboxes(N as integer)
dim i as integer

For i=1 to 25
  If i<>N then 
       Me("chk" & i)=true
       Me("chk" & i).visible=true
  Else
       Me("chk" & i)=false
       Me("chk" & i).visible=False
  End if
Next i

End function

[Gicu]

Thanks Colin, you're right, I missed that in his second post. I think your edit will achieve Paul's intended result.

Sorry to take it in a wrong direction!

Cheers,
Vlad

[Micron]

I did something like this long ago; it was a risk matrix for ISO 14001. Actually, it was a product that I developed but only sold one copy of (timing was bad - everybody seemed top want web-based apps and I wasn't willing to devote the time to learn that side of things). The db was called Aspectrum.

I can't recall for sure but I think the matrix was just a graphic on the form and the risk elements were table records. In keeping with good db design, the product of the numbers (score) was a calculation done on the form and was not stored (which you are doing: "This value is stored ")I think the individual factors (risk/consequence) were as important as the product of the factors, especially since many scores can be the same when the factors are reversed. That approach also allows you to know which one(s) have changed if the score changes. I throw that out here because I get the impression that you're taking a different approach and might want to consider others.

Besides, in your approach 4 * 2 versus 2 * 4 are both yellow, but they are not the same control, so how are you going to decide on which one to set a T/F value for?

[Gicu]

Micron, I see what you're saying about 4*2 vs. 2*4. I think the function would still work, but it would have to be called differently:
call DealWithCheckboxes(Me.combo1&me.combo2) instead of call DealWithCheckboxes(Me.combo1*me.combo2).

With that change and proper naming of the check-boxes (in this case chk24 and chk42) I think it would work And I am not sure why you say the value is stored, I assumed Paul's check-boxes are unbound controls.

Cheers,
Vlad