DB Structure opinion

[dniezby]

Right now I have a table with a field "active contacts". These are contacts that I added to my database that were collected through a marketing source.



I'm thinking that this is an unnecessary field now. However, I want that total to be shown on the form.

So, how could I create a query that looks at that field on the form (Me.TrackingCode), then performs a query that counts the number of people in a different table that have that tracking code that is equal to the cboTrackingCode result.

[andy49]

Create a textbox and type in it

= dcount("activecontacts","tblcontacts", "where activecontacts =" & me.trackingcode


Sent from my iPhone using Tapatalk

[dniezby]

What about the field? You think I should store that info or just total it up when I need to? I'm starting to think the later.


Sent from my iPhone using Tapatalk

[andy49]

Will depend on quantity of data I think. But as a general rule, don't calculate data if you don't actually need to.


Sent from my iPhone using Tapatalk

[dniezby]

Create a textbox and type in it

= dcount("activecontacts","tblcontacts", "where activecontacts =" & me.trackingcode


Sent from my iPhone using Tapatalk

I think I have something out of order or named wrong.

Here is field table one with the contacts ( tblContacts):

Campaign_ID


Here is the actual campaign table. (tblCampaigns ):
ID - Which is the related field to the contact.

Here is the Textbox on the form. ( txtActiveContacts )

Is this how I should put this on the form?
Private Sub Form_OnLoad()

'## Check the Campaign Code and show how many contacts have resulted.
' If this is not a new record then
    If not New.Record then
    'Check the contact's Campaign ID, add the total records and post the result to txtActiveContacts
      txtActiveContacts.Value = dcount ( "Campaign_ID","tblContacts", where "Campaign_ID" = Me!ID)
    End If

[CJ_London]

it should be

dcount ( "Campaign_ID","tblContacts", "Campaign_ID =" & Me!ID)

Also, it may need to be in the current event.

And you don't need .value - that is the default property for a field. However if txtActiveContacts is an unbound control, then it should be

txtActiveContacts.controlsource="=dcount ( 'Campaign_ID','tblContacts, 'Campaign_ID = " & Me!ID & ")"

but if that is the case you might as well set the controlsource in form design view and not bother with the code

[dniezby]

it should be

dcount ( "Campaign_ID","tblContacts", "Campaign_ID =" & Me!ID)

OMG LOL, thank you. So the "where" part is already understood huh...Also helps to have the quotes in the right place.
Thank you. It seems to have worked.

[dniezby]

it should be

dcount ( "Campaign_ID","tblContacts", "Campaign_ID =" & Me!ID)

BTW I really hate the way Microsoft explains how to use these features.

Why couldn't they say:

DCount ("FieldToLookUp","TableToLookIn", "FieldToLookUp =" & FieldToCompare)

Much easier to understand where the info comes from than:

DCount (expr, domain [,criteria])

Maybe it's that I don't understand the use of domain or expr ?

[dniezby]

Actually, it half worked.

It does give me the correct total for the first record but if I go to the next record, it still gives me the same number. So, apparently, it's not retotaling when I change the record.

How do I get it to do that?

Also, I tried putting this actually into the textfield

= DCount("Campaign_ID", "tblContacts", "Campaign_ID =" & Me!ID)

And it gave me an error #Name?

So, I went back to using it in the Form_Load() and it works...Just doesn't update as I change records.

If Not Me.NewRecord Then
txtActiveContacts.Value = DCount("Campaign_ID", "tblContacts", "Campaign_ID =" & Me!ID)
End If

[dniezby]

Okay, I FIXED IT!!!

I just moved the code to Form_Current()

Now it retotals for each record.

[CJ_London]

expr, domain and criteria are standard expressions and terminology and in most cases clearly defined. e.g. from the MS webpage for DCount

DCount(expr, domain, [criteria])The DCount function has the following arguments.

Argument	Description
expr	An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain	A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria	An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.

And as regards "FieldToLookUp" etc. If you have spaces it would be "[Field To Look Up]", if a calculation it would be "Field1 * Field2" or "[Field 1] * [Field 2]" and if using a variable it would just be the name of the variable, no quotes or square brackets. Tables can be tables or queries (without parameters) and again with or without square brackets depending on spaces (or non alphanumeric characters for that matter).

For examples which are specific, you need to look to the forums for the same or similar questions to see specific examples. And sometimes you just need to experiment until you fully understand what code you are using and why you are using it.

I could say the same about posters questions - why can't they say what the name of their table is, or whether a control is bound or unbound? And why do they insist code provided 'doesn't work' when they did not provide all relevant names, any errors etc, so they get responders best guess as to the fields are actually called and posters try to use that directly in their code.

Not so long ago I provided a solution along the lines of

theNameOfYourControl.backcolor=vbred

the poster came back with 'it doesn't work'. On asking what the error was it was '3305'. On asking what the description was, it was 'theNameOfYourControl not recognised'. Go figure

[CJ_London]

Okay, I FIXED IT!!!

I just moved the code to Form_Current()

as I suggested you may need to do. Without knowing how your form works.....

[dniezby]

Wow great explanations. Also thanks for the help. I didn't even see that I should move it to the current event. Lol. That is until you mentioned it here and I went back and said, "I'll be damned".


Sent from my iPhone using Tapatalk