Increment Subroutine for multiple fields

[Banana]

Hello, I tried to search this forum, but didn't find what I want to do

I have a database that will count several categories (as # of requests per). For sake of UI, I want to use a button so user can click on it, and the corresponding value would automatically increment by one. I have code for it, but rather than coding it for each individual button, i want to have one global subroutine that can call for fieldname, then increment that field by one.

The buttons will have the same name as fields, so I only need to get a code that can lookup button's name then relate it to the right field.

Is that possible?

Many thanks!

 

[WayneRyan]

Banana,

Are you saying that you have a # in four fields and that when a
field's associated Command Button is pressed, the appropriate
field should be incremented by 1?

If the four buttons each had one line of code:

Me.Field1 = Me.Field1 + 1
Me.Field2 = Me.Field2 + 1
Me.Field3 = Me.Field3 + 1
Me.Field4 = Me.Field4 + 1

That's about as optimum as you can get --> 4 buttons ... 4 lines of code.

It is definitely possible for the Command Buttons to call a common
routine. The common routine could use something like ".ActiveControl"
or be passed a parameter, but it will ultimately be more code than
the original one-line per button.

Now, if the four fields being incremented are to use something like
the DMax function (+1) to get the values, that's a different matter.

Need more info,
Wayne

 

[Banana]

Wayne

You got it right- that is how my code stands at the present (me.field1 = me.field1 +1).

However, I have about 100 individual fields, which will be divided into 20something categories, which will have their own subforms to keep things neat and tidy much as possible. So we're talking about 20 subforms linked to one form (of general categories), which itself is a subform to a yet another field.

The first form is essentially a demographic info of a client, with some fields asking what kind of services this particular client requested. One service, we are required to count numbers of requests, what kind of requests, per clients.

I could code it 100 times over for each individual fields, but I think that would result in 1) problematic troubleshooting if we had to revise database at a future date, 2) too much time and labor.

Dmax function sounds promising. Can you explain more?

 

[Banana]

After looking some more, I found this thread, which gives the subroutine I think I want.

But when I inserted either code, I either get a error message or nothing happens at all.



Here is the code I'm using but has no results-

Public Sub Inc(ByRef varValue As Variant, varIncrement As Variant)
varValue = varValue + varIncrement
End Sub

....

Private Sub AgricultureCounter_Click()
On Error GoTo Err_AgricultureCounter_Click

Inc Agriculture, 1

Exit_AgricultureCounter_Click:
Exit Sub

Err_AgricultureCounter_Click:
MsgBox Err.Description
Resume Exit_AgricultureCounter_Click

End Sub

The above code compiles fine, but does nothing whatsoever. Same if I change the code to:

Dim Agriculture As Integer
Inc Agriculture, 1

Any explanations?

 

[WayneRyan]

Banana,

Inc has to be a function that RETURNS a value.

Since they are integer values, why are they declared as variants?

It still doesn't reduce the total lines of code.

Public Function Inc(ByRef varValue As Variant, varIncrement As Variant) As Long
  Inc = varValue + varIncrement
End Sub



Private Sub AgricultureCounter_Click()
On Error GoTo Err_AgricultureCounter_Click

Me.Agriculture = Inc(Me.Agriculture, 1)

Exit_AgricultureCounter_Click:
Exit Sub

Err_AgricultureCounter_Click:
MsgBox Err.Description
Resume Exit_AgricultureCounter_Click

End Sub

Wayne

 

[Banana]

So essentially the original code I had is probably the best I'll have?

Considering that there'll be 100something fields to code for, it seems a bit strange, but if that's what it is, then....


Thanks so much!

 

[WayneRyan]

Banana,

I'm having some difficulty visualizing what should be happening.

It is possible to have 100 fields on a form.

1) Move to some field.

2) Click on a Command Button

3) Have the Command Button increment the previous field by 1.

Command Button:

Me.Screen.PreviousControl = Me.Screen.PreviousControl + 1

Just a first stab at it ... the prior responses were for your initial
specific question.

Wayne

 

[Banana]

Wayne,

Thanks.

Let me elaborate.

In master form, there is one checkbox "informational and referral", which when it is checked, opens a subform listing 20 general categories. All are checkboxes, save for three buttons as they do not have specific categories.

Checking any one of checkbox then opens another subform listing specific categories of that particular category. That is where we will have command buttons next to every textbox for incrementing #s of request per client.

This is where I am bit skeptical with the original codes I have; with three subforms, the need to count each requests for all categories, per client, seem to me that a subroutine best serves my needs. Of course, I could be wrong and am better off with the X=X+1 code.

Clear as mud?

 

[WayneRyan]

Banana,

So a typical subform has:

CatX   Specific1  Count
CatX   Specific2  Count
CatX   Specific3  Count
CatX   Specific4  Count
.
.
.
CatX   SpecificN  Count

As an aside, I think you need tables like this:

tblClients
==========
ClientID - AutoNumber
ClientName

tblCategories
=============
CategoryID - AutoNumber
CategoryName

tblSpecifics
============
SpecificID - AutoNumber
SpecificName
CategoryID - FK to tblCategories

tblDetails
==========
DetailID - AutoNumber
DetailDate
ClientID - Fk to tblClients
CategoryID - Fk to tblCategories
SpecificID - Fk to tblSpecifics

Then, you don't even store counts.
Your queries/forms/reports will total them for you.


However, using the current configuration that you have you can
do something like:

1) Use the Double-Click event of each control to make it green
for visualization

2) Have a command button to loop through all controls incrementing
the green ones and turning them back to black.

However, you still need an event for EACH control!

The only ways that don't require individual events (or one-line code things)
don't give you any visual feedback.

Maybe you can strip down your DB and post it. Some ideas might be
forthcoming if myself (and others) see it.

hth,
Wayne

 

[Banana]

Wayne

Good thing I already kept everything in their own tables as you suggested. Every subform has their own tables with FK to the clients table. I have yet to implement specific table/form, as I'm still deciding on how I want to develop this.

Ill see if I can post my DB- how would I do it, as my email reject it as a virus?

Thanks and enjoy the weekend.

 

[WayneRyan]

Banana,

Tools --> Compact/Repair
Then ZIP
Then attach ("Manage Attachments") at bottom of post.

Wayne

 

[Banana]

I deleted several details about the database to make it generic for privacy reasons. However, the design is fundamentally same.

And to reliterate, the second subform for specifics isn't implemented yet as I'm still deciding how to work this out (mainly the increment routine as I asked before)

 

[Summerwind]

Hope you will forgive me but I think I would approach this in a different way.

As I understand it you have 20 categories. Why not have a combobox on your main form holding the 20, based on a table rather than coded into the control so to allow for easy future development.

The AfterUpdate event reveals a second combo based on another field in the afore-mentioned table and relevant to the first choice.

AfterUpdate event on second choice updates the count table.

No sub-forms and a lot less code than that for 100 buttons I think??

Regards

 

[Banana]

Summerwind,

thanks for the suggestion.

The real problem here is I have to *count* each requests each clients. In other words, if a client makes request for information on 3 separate topics about oh say state law, it'll be count three times (once as general category of law and enforcement, three time under specific category of state law), for one client.

If combobox can count more than once, that'd be cool.

 

[Summerwind]

I'm sorry I don't have time to look at your app but I assume that you have a table somewhere that records how many times your client has looked at whatever. A "Count" field.

I would work along the lines of having the Count field of the main category being seen incremented in the AfterUpdate (or OnChange) event of the first combo.

Client looks at sub-category. Code increments Count field of sub-category in the AfterUpdate event of the second combo.

You could also use this second event to direct your client to the required information (If you haven't already)

Cheers

 

[Banana]

Summerwind, thanks for the suggestion. I'll look into this more and decide if this is something I want to implement.

Meanwhile, I had an idea- Based on the same idea that everyone use to disable all Access menu bar and commands by using a For...Next loop-

Is it at all possible to create a For...Next loops for each subforms, generating a custom subform, by looking up all the records, adding a corresponding command button with one line code (let x=x+1), with auto resize and auto center.

That'd making subsequent changes to records format easy as pie. But this is beyond my programming abilitiy and I have no idea if this is even possible. Can anyone enlighten me?

Something like this:

Docmd.NewForm
Dim i As Integer
For i = 1 To Specifictablerecords.Count
Create commandbutton ByRef records
click() let byref records = records + 1
Next i
Auto Size
Auto Center
Create commandbutton "OK"
Create commandbutton "Cancel"

 

[Banana]

Summerwind,

I tried the combo or list box. Turns that there's a limit of 20 fields per box, and there's lot more than 20 for general. I could break it up, but I think it'd have negative impact on UI. It's important that the form is clean and simple to use.

But I'm also trying to avoid hard-wiring the form too much that any subsequent revisions would take hours long....

So any more ideas?

 

[Summerwind]

Hmmmm, I obviously didn't explain too well.

The first combo is based on a table of main categories:

Fields in tblMainCats: CatNo (Key to table and first column, which is hidden in first combo), CatName (Second column in combo)

Have a table of sub categories with SubCatNo,SubCatName,MainCat etc


MainCatCombo AfterUpddate:

............
Dim strSource As String
strSource = "SELECT SubCatNo, SubCatName FROM tblSubCats WHERE MainCat = "
& Me.MainCatCombo.Value & "ORDER BY SubCatName"
with me.SubCatCombo
.RowSource = " & strSource
.SetFocus
.DropDown
End With


User selects a sub cat in combobox

Run code to increment Count fields in the table recording "Hits" on the main cat and the sub cats.

User selects another sub cat, code runns again from AfterUpdate event of second combo.

You really don't need all of those sub forms etc and you have a clean input mechanism

 

[Banana]

Okay.

I tried to set up combo boxes as per instructions.

The trouble is while first combo box works perfectly, the second combo box remains blank no matter what.

Ive tried to cut and paste the code, tried it different ways, using info from Cascading Combobox in FAQ and other websites. The endresult is still same; a blank second comob box...

So what now?

Edit= There's a possible clue= there are currently no records, but there seems to be 233 records when I made combo boxes. Maybe I messed something up?

Edit #2= Ahh, I can see why- Somehow the query is adding more records to the Specific table, as opposed to putting defined values in 2nd combo box... Why?