Calculate Total then Compare using forms

[tuoggy]

Okay, so I don't know if this is possible, or what I must do if it is. Here is what I am trying to do. I run a database which tracks student registration at camp. One of the things we do is assign students to certain cabins. These cabins each have a limited capacity. So, in one table, I listed the cabins, whether they are male or female, and the max capacity. In the primary table we list the Names and info of the students and assign a cabin through a combo box. Is there any way to automatically sum up the number of people per cabin, and compare that to the Cabin's max number when inputing in the form (want to run a macro letting the person inputing that there is no more space available)? Also is there a way to compare the gender to run a macro if the gender of the cabin and the person don't match?

 

[ajetrumpet]

So, in one table, I listed the cabins, whether they are male or female, and the max capacity. In the primary table we list the Names and info of the students and assign a cabin through a combo box. Is there any way to automatically sum up the number of people per cabin, and compare that to the Cabin's max number when inputing in the form (want to run a macro letting the person inputing that there is no more space available)? Also is there a way to compare the gender to run a macro if the gender of the cabin and the person don't match?

Yes, but not with Macros. Here is the code (to cover both requests) to use with a form event...

If DSUM("[StudentNameField]", "PrimaryTable", 
  "[StudentNameField] = Forms!YourForm!YourComboBoxName") >= 
    DLOOKUP("[MaxCapacityField]", "CabinsTable", 
      "[CabinNameField] = Forms!YourForm!ControlNameThatHoldsTheCabinName") Then
        Msgbox "This cabin is already full!", vbOkOnly

ElseIf
  
Me.ControlThatHoldsTheGenderValue <> DLOOKUP("[CabinTypeField]", "CabinsTable", 
  "[CabinNameField] = Forms!YourForm!ControlNameThatHoldsTheCabinName") Then
    Msgbox "This cabin is not available to the gender specified, please select another gender", vbOkOnly

Else
  DoCmd.Save ([I][COLOR="Red"]this command enters the record and goes to a new one[/COLOR][/I])

 

[tuoggy]

Thanks Adam,

I have done everything how I think it should be done, but I am getting syntax errors. Forgive my newbishness, I'm not used to VBScript so it's taking some time to get used to.

Here is the code that I have used,

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DSUM("[Last Name]", "Participants",
  "[Last Name] = Forms!Participants Data entry!Cabin") >=
    DLOOKUP("[Capacity]", "Cabins",
      "[Cabin ID] = Forms!Participants Data Entry!Cabin") Then
        MsgBox "This cabin is already full!", vbOKOnly

ElseIf
  
Me.[Gender] <> DLOOKUP("[Gender]", "Cabin",
  "[Cabin ID] = Forms!Participants Data Entry!Cabin") Then
    MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
  DoCmd.Save
End Sub

The structure for the tables are set up like this:

Cabin (Table)
FIELDLIST:
Cabin ID
Cabin Name
Gender
Capacity

Participants (Table)
FIELDLIST:
ID
Last Name
First Name
Gender
Cabin

The Form name is [Participants Data Entry]

The form that I am using is a very simple form that I just used the Wizard to move everything over from the Participants table. I placed the Code in the Before Update Event box. What do I need to do to make this code work?

This has been driving me nuts for some time now, and I haven't been able to find anything online that can help so far.

 

[ajetrumpet]

Fix the code in red and the syntax errors will go away...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DSUM("[Last Name]", "Participants",
  "[Last Name] = Forms![B][COLOR="Red"][SIZE="3"][[/SIZE][/COLOR][/B]Participants Data entry[B][COLOR="Red"][SIZE="3"]][/SIZE][/COLOR][/B]!Cabin") >=
    DLOOKUP("[Capacity]", "Cabins",
      "[Cabin ID] = Forms![B][COLOR="Red"][SIZE="3"][[/SIZE][/COLOR][/B]Participants Data Entry[B][COLOR="Red"][SIZE="3"]][/SIZE][/COLOR][/B]!Cabin") Then
        MsgBox "This cabin is already full!", vbOKOnly

ElseIf
  
Me.[Gender] <> DLOOKUP("[Gender]", "Cabin",
  "[Cabin ID] = Forms![B][COLOR="Red"][SIZE="3"][[/SIZE][/COLOR][/B]Participants Data Entry[B][COLOR="Red"][SIZE="3"]][/SIZE][/COLOR][/B]!Cabin") Then
    MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
  DoCmd.Save
End Sub

Multiple-word strings/statements in SQL and VBA must be "grouped" by a valid character. Field names (without the table name after them), and object references in Visual Basic need [ ] or { } to group them, but only if they contain spaces.

Forgive my newbishness

Newbishness!? You're better than some!

 

[pbaldy]

I hesitate to mention this, but as noted in Access Help, DoCmd.Save will not save a record, rather it will:

save either a specified Microsoft Access object or the active object if none is specified

In other words, a form, report, etc. To save a record:

DoCmd.RunCommand acCmdSaveRecord

To go to a new record, you would use:

DoCmd.GoToRecord

with the appropriate argument(s). Going to a new record should force a save of the current record, so you wouldn't need both.

 

[tuoggy]

I'm still getting the syntax error. After I get the error it highlights the top line. Is it a problem that it's set for Before Update? Do I need to create a button to click to run the code and go to the next record? Currently I just Tab or Enter through the form.

And every line is red in the VB editor except the msgbox lines, if that tells you anything.

 

[ajetrumpet]

To go to a new record, you would use:

DoCmd.GoToRecord

with the appropriate argument(s). Going to a new record should force a save of the current record, so you wouldn't need both.

Thanks for the correction Paul.

Is it a problem that it's set for Before Update?

Probably. I believe the purpose of that event is to perform the code when a control is updated, but not taking into account the value that the control was updated to. So, if you opened your form, and the controls were blank, selecting a value from a combo box right way would trigger this error. Try using any other (subsequent to a value change) event. A button would work the best. That way, all of your values would be entered in the controls before the code is run. The code needs the values to evaluate everything correctly, so try it this way and see what happens.

And, BTW, I entered the wrong function!!!! Sorry!! The code should read...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If [COLOR="Red"][B][U]DCOUNT[/U][/B][/COLOR]("[Last Name]", "Participants", etc...

Also, you're comparing an aggregate (the RecordCount) to the "Capacity" field, so make sure that field is a numeric data type.

 

[pbaldy]

And every line is red in the VB editor except the msgbox lines, if that tells you anything.

I suspect Adam didn't notice this. If you have it in code like you have it here, you need line continuation characters (a space and an underscore) when you split a single clause onto multiple lines. It might be simpler for you to keep it all on one line until you get it to work, so you're not trying to figure out 2 things at the same time.

 

[ajetrumpet]

Good point. I think it's quittin' time for me! I don't know how many times you've nailed me Paul, but I need to start reducing the number "audit suspect" posts...

Anyway, with Paul's help, the final code should read (with the functions on one line)...



Private Sub Form_NewEventBesidesBeforeUpdate(Cancel As Integer)

If DCOUNT("[Last Name]", "Participants", "[Last Name] = Forms![Participants Data Entry]!Cabin") >= DLOOKUP("[Capacity]", "Cabins", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
(new line here) MsgBox "This cabin is already full!", vbOKOnly

ElseIf

Me.[Gender] <> DLOOKUP("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
(new line here) MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
DoCmd.Save
End Sub

 

[tuoggy]

Okay, still on this issue...

Here's the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[Last Name]", "Participants", "[Last Name]=Forms![Participants Data Entry]!Cabin") >= DLookup("[Capacity]", "Cabin", "[Cabin ID]=Forms![Participants Data Entry]!Cabin") Then
MsgBox "This Cabin is already full", vbOKOnly

Else

Me.Gender <> DLOOKUP("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
DoCmd.GoToRecord
End If
End Sub

With this code I get:
Compile Error: Expected Expression

And the <> gets highlighted.

Also, the first part doesn't cause an error, but when I tried to test it with just the first part, it didn't do anything when the count of people in one of the cabins exceeded the limit set up in the Cabin table.

First of all, thank you so much for all your help already. You have gotten me so much farther than I could have done on my own.

 

[Rabbie]

]Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[Last Name]", "Participants", "[Last Name]=Forms![Participants Data Entry]!Cabin") >= DLookup("[Capacity]", "Cabin", "[Cabin ID]=Forms![Participants Data Entry]!Cabin") Then
MsgBox "This Cabin is already full", vbOKOnly

 [COLOR="Red"]ElseIf[/COLOR] Me.Gender <> DLOOKUP("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
DoCmd.GoToRecord
End If
End Sub

That should fix that error.

 

[ajetrumpet]

Tuoggy, instead of what you have written here, I would just Nest two IF statements together, like this...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[Last Name]", "Participants", "[Last Name]=Forms![Participants Data Entry]!Cabin") >= 
   DLookup("[Capacity]", "Cabin", "[Cabin ID]=Forms![Participants Data Entry]!Cabin") Then
      MsgBox "This Cabin is already full", vbOKOnly

Else[COLOR="Red"][B]If[/B][/COLOR] Me.Gender <> DLOOKUP("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
   MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
  DoCmd.GoToRecord [COLOR="Red"](You need more syntax here - e.g. what record?)[/COLOR]
End If
End Sub

You forgot the "IF" on the end of the first "ELSE". You need "ElseIf", if there are more than two conditions to an IF statement. If you need an explanation for any of this, just say so.

Are you about "If'd out" by now?

 

[tuoggy]

Okay that fixed the gender field!!!

There are no more Syntax errors. The final remaining problem I'm having is with the Capacity MsgBox. For some reason it isn't comparing the DCOUNT to the Capacity in the Cabins Table.

Any Ideas?

<edit> Okay, what I meant by the previous statement is that when the Participants in Cabin X exceed the Capacity of Cabin X the MsgBox doesn't pop up. I don't think I made that clear in the previous statement. Thanks again everyone!!!</edit>

 

[ajetrumpet]

when the Participants in Cabin X exceed the Capacity of Cabin X the MsgBox doesn't pop up.[/code]This doesn't make sense, as the code, the way it is written, would not cause this. Can you post a file, or a screenshot the two tables "Participants", and "Cabins". The structuring of your records/data might be the source of the confusion here.

 

[tuoggy]

I have attached the 3 images. The Participants has all the non applicable fields hidden, and the input Form is just an initial Wizard made form.

Hope this is what you needed

 

[ajetrumpet]

Tuoggy,

I'm changing the code a bit. Maybe, during all the conversation, I misunderstood your problem. I changed the condition and the field (just in case there was duplicates in the "Last Name" field) in the DCOUNT function. I'm afraid it was referencing the wrong field. Try running this procedure instead...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[[color=red]ID[/color]]", "Participants", "[[color=red]Cabin[/color]]=Forms![Participants Data Entry]!Cabin") >= 
   DLookup("[Capacity]", "Cabin", "[Cabin ID]=Forms![Participants Data Entry]!Cabin") Then
      MsgBox "This Cabin is already full", vbOKOnly

ElseIf Me.Gender <> DLOOKUP("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
   MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
  DoCmd.GoToRecord
End If
End Sub

 

[tuoggy]

That did it!

Thanks again Adam and everyone who helped. You guys are great!!!

 

[tuoggy]

I'm back, same problem different line.

So now that I have gotten the code in and we are testing the database (for some reason this didn't turn up in our initial testing) I am getting an error message when I try to update the record and move to the next one.

Run-Time error '3075':
Syntax error (missing operator) in query expression '[Orientation
Leader/Group]=Forms![Participants Data Entry]!Orientation 
Leader/Group'.

Here is the code that I am using

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[ID]", "Participants", "[Cabin]=Forms![Participants Data Entry]!Cabin") >= DLookup("[Capacity]", "Cabin", "[Cabin ID]=Forms![Participants Data Entry]!Cabin") Then
MsgBox "This Cabin is already full", vbOKOnly

ElseIf DCount("[ID]", "Participants", "[Orientation Leader/Group]=Forms![Participants Data Entry]!Orientation Leader/Group") >= DLookup("[Capacity]", "OL", "[OL ID]=Forms![Participants Data Entry]!Orientation Leader/Group") Then
MsgBox "This OL's group is already full", vbOKOnly

ElseIf DCount("[ID]", "Participants", "[Activity]=Forms![Participants Data Entry!Activity") >= DLookup("[Capicity]", "Activities", "[Activity ID]=Forms![Participants Data Entry]!Activity") Then
MsgBox "This activity is already full", vbOKOnly

ElseIf Me.Gender <> DLookup("[Gender]", "Cabin", "[Cabin ID] = Forms![Participants Data Entry]!Cabin") Then
MsgBox "This cabin is not available to the gender specified, please select another gender", vbOKOnly

Else
DoCmd.GoToRecord
End If
End Sub

I just want this project to be finished

 

[pbaldy]

Everything with the inadvisable spaces and symbols needs to be enclosed in brackets. Also, the form references should be concatenated in, with single quotes if appropriate:

http://www.mvps.org/access/general/gen0018.htm

 

[tuoggy]

Got it, it works, now I can move on to something else.

Thanks again