Frustrations with DCount, creating a counter and solving #Name? errors

[daveybaggio]

Hi,



I am very new to using Access and databases in general. I have been playing around to try to gain an understanding but there are huge gaps in my knowledge.

I would like to use DCount to count the number of records with a particular value in one of the fields of one of my tables. I looked at the syntax for DCount and it all seemed straightforward.

I am trying create this counter on a form. I have created a form ("form2020Events") which corresponds to my "2020Events" table. I would like the form to display a counter of all bookings made for that event. (I also have a "2020Bookings" table). When I try to use the following control source for the text box on my form I get a #Name? error:

=DCount("*","2020Bookings", "Event='Party'")

When I use:

=DCount("*","2020Bookings")

I get a count of all the records in 2020Bookings.

Could this be because the "Event" field in 2020Bookings is a lookup field (to the "2020Events" table)? I thought this might be the case so I changed the condition to equal the ID instead (='Party' to ='1'), the Event ID but that still didn't solve the issue.

So I tried on a new database replicating this and found I could create a counter if the lookup wasn't in place (ie the Event field was manually entered as text rather than looked up from the 2020Events table). However, by doing this I have introduced the possibility of making a typo when manually entering the Event to a new booking, instead of selecting from only the events stored in the table "2020Events".

I then tried solving the problem by creating the Event field in "2020Bookings" with data type = Short Text (instead of a lookup). Then in the corresponding form for adding new bookings to the table, I created a Combo Box to lookup the "Event". I.e. creating the lookup on the form rather than in the table. That didn't help either.

At this point my brain was completely befuddled and loosing all grip on the changes I have made...

More generically, what I am asking is what is the best way to create a counter on a form, to calculate the number of records with a specific field value in a table, when those entries are created using a lookup to a table? And if the lookup is the problem, what is the best way to set up the entries into that field such that they are limited to the entries defined in another table?

Hope that made some sense...

I have done a lot of "googling" and scouring forums for something to help but I haven't found a solution.

Thanks in advance for any help given.

[ranman256]

=DCount("*","2020Bookings", "[Event]='Party'") is correct, as long as EVENT is the name of the field, and no other filters are in the query that would alter the count.

you can also use a control on a form:

=DCount("*","2020Bookings", "[Event]='" & me.cbBox & "'")

[Gicu]

The lookup fields should be avoided when you design your tables as users should not be allowed to interact with tables (here is a bit of reading on that: http://access.mvps.org/access/lookupfields.htm). So you are close to the accepted solution with your combo box. For the combo box you need to set its Row Source Type to table/query, Row Source to "SELECT EventID,EventName FROM 2020Events" (note that the structure of your db doesn't seemed to be normalized, you shouldn't have tables with names like 2020Events because you will need to create a new one next year and modify a whole bunch of related objects, use instead a generic tblEventsT with a year field). The column should have the Column Count set to 2, Column Widths set to 0";3" (to hide the EventID), bound column should be 1 (the EventID field) and the control source to be an Event(ID) field in the 2020Bookings table set to number (long).

EDIT: my suggestion above is for the data entry. not the dCount which would become dCount("*","[2020Bookings]","[Event] =" & Me.cboEvent) if the field is numeric).

Cheers.

[daveybaggio]

Thanks for the help guys. I will give that a go. I had read that Look-ups were "bad" but could work out why. They seem such an easy way to a beginner...

[daveybaggio]

The lookup fields should be avoided when you design your tables as users should not be allowed to interact with tables (here is a bit of reading on that: http://access.mvps.org/access/lookupfields.htm). So you are close to the accepted solution with your combo box. For the combo box you need to set its Row Source Type to table/query, Row Source to "SELECT EventID,EventName FROM 2020Events" (note that the structure of your db doesn't seemed to be normalized, you shouldn't have tables with names like 2020Events because you will need to create a new one next year and modify a whole bunch of related objects, use instead a generic tblEventsT with a year field). The column should have the Column Count set to 2, Column Widths set to 0";3" (to hide the EventID), bound column should be 1 (the EventID field) and the control source to be an Event(ID) field in the 2020Bookings table set to number (long).

EDIT: my suggestion above is for the data entry. not the dCount which would become dCount("*","[2020Bookings]","[Event] =" & Me.cboEvent) if the field is numeric).

Cheers.

Thanks for that.

I started from scratch with no lookups and normalised as you suggested with a year field.

I then came back to the dCount on my Event form. I still can't get it to work. I get a #Name? error showing. It can count a total of all bookings, using =DCount("*", "Bookings"), without filtering for a specific event, but when I try it to count specific event totals I get the error. I am not sure at all how the &me.cboEvent works or how that would filter for a specific event but I played around with it for a while and couldn't get it to return a count. I also tried

=DCount("*", "Bookings", "Event = 'TestEvent'") and

=DCount("*", "Bookings", "Event = '1'") to no avail. (My tester booking is for the Event "TestEvent" with EventID "1".

Perhaps I am looking at this all wrong and the Event form is not the place for my counter. It would be just good to have a reference for bookings to each event that can be a visual aid when adding a new booking (ie to check there is space left on that event).

Cheers for any help

[dashiellx]

Rather than trying to count the whole record, perhaps just count a single field:

Try: =DCount("Name of an actual field", "Bookings", "[Event] = 'TestEvent'")

I have also had better success when putting square brackets around the criteria field names.

hth

[daveybaggio]

I have no idea what I have done but it is working now! The more the learn, the more you learn that there is loads of stuff you don't know... Thanks for your help folks.

[daveybaggio]

Rather than trying to count the whole record, perhaps just count a single field:

Try: =DCount("Name of an actual field", "Bookings", "[Event] = 'TestEvent'")

I have also had better success when putting square brackets around the criteria field names.

hth

I think it might be the square brackets that did it! I kept searching for the whole record with the Asterix in the end but I will bear that in mind for the future. Cheers.

[Gicu]

Can you please post a small sample of your db (remove any confidential data, just leave the two tables and the form) to save us to recreate it? If you want to show the count of bookings for the current event loaded in the form and and assuming you have a combo box control named "Event" (in design view look at the event combo's name, not the control source) that is bound to a EventID numeric field in the 2020Bookings table and the first (hidden) column in its row source is the EventID (numeric, long) from the 2020Events table then in a text box you add =Dlookup("*","2020Bookings","[EventID]=" Me![Event]).

Cheers,

[daveybaggio]

Can you please post a small sample of your db (remove any confidential data, just leave the two tables and the form) to save us to recreate it? If you want to show the count of bookings for the current event loaded in the form and and assuming you have a combo box control named "Event" (in design view look at the event combo's name, not the control source) that is bound to a EventID numeric field in the 2020Bookings table and the first (hidden) column in its row source is the EventID (numeric, long) from the 2020Events table then in a text box you add =Dlookup("*","2020Bookings","[EventID]=" Me![Event]).

Cheers,

I thought I had found a solution last night but it didn't work in the end. The counter returned a number but then it returned the same number on each new Event (ie I could only count one event and not the event specific to the current form.

I have simplified the DB and attached it. What I am trying to achieve is a counter in the Events form (formEvents) which allows me to see how many bookings have been made for that specific event. Ideally I would like to store that count in a field on the Events table, so that when I select an event on the bookings form (formBookings), using a Combobox, I can see the event name, date and current number of bookings. As yet I haven't added the field to store this in.

I have tried the above Dlookup but I just get a #Error in the text box.

Really appreciate your help. Cheers

Database5.zip

[Bob Fitz]

The following expression works as the Control Source for the field called "Text14":

=DCount("BookingID","Bookings","[Event] = " & [EventID])

but I think your tables need to be looked at.

[Bob Fitz]

Your Bookings table should have number fields to store the EventID key field and the CustomerID key field rather than a descriptive field from each table.

EDIT:

Sorry, please ignore the statement above.

IMHO it would be better to name the fields in the booking table as EventID and CustomerID or better still EventIDfk and CustomerIDfk

I would also remove the space from the field called "Start Date" in the Events table and prefix table names with "tbl".

[daveybaggio]

The following expression works as the Control Source for the field called "Text14":

=DCount("BookingID","Bookings","[Event] = " & [EventID])

but I think your tables need to be looked at.

Brilliant - that works well, thank you!

[daveybaggio]

Your Bookings table should have number fields to store the EventID key field and the CustomerID key field rather than a descriptive field from each table.

EDIT:

Sorry, please ignore the statement above.

IMHO it would be better to name the fields in the booking table as EventID and CustomerID or better still EventIDfk and CustomerIDfk

I would also remove the space from the field called "Start Date" in the Events table and prefix table names with "tbl".

Thanks for the pointers, Bob. I am very new to this so this is all good to learn.