Problem with DCOUNT

[Micron]

[PerformedOn] = #11/1/2021# AND [Machine] = 1

There is no record with that date in your sample db.
If that's what you've been working with all along, then that date from your post is invalid? I get a DCount value of 1 if I use the date in your record. Am I missing something?

[smaier]

There is no record with that date in your sample db.
If that's what you've been working with all along, then that date from your post is invalid? I get a DCount value of 1 if I use the date in your record. Am I missing something?

During my paring down the db to make it small I got rid of most lines of data and proactively changed the date to Nov 2nd. If you change the value to today's date you will likely get the same behavior.

Edit: Just checked and am not having the same issue. Or, no issue at all. What the holy heckfire?!?

[Micron]

Today's date seems to work as you have noticed. It would seem that by removing stuff you've eliminated the issue. Need to step back I suppose.
You're not going to have a table for each one of the table variables shown in those tests I hope.

[smaier]

... it looks like the code is working now in this simplest state. I can assure you there were appropriate dates in the table from the outset. The only thing I can think of is what is now a textbox with the machine number was previously a combobox which was bound to a different table to populate its values.

I feel like punching myself for wasting everyone's time. When I get to work tomorrow I'll take the time to look more closely at the properties of that control to see what the problem was.

It's just perplexing that the msgbox checks to see the variables' values looked to be fine.

[smaier]

You're not going to have a table for each one of the table variables shown in those tests I hope.

No, thankfully. I went this route so I wouldn't have to create multiple queries for each machine type we have.

And at this point I totally agree, I need to take a step back and focus on something else.

Thank you as well as everyone else for the help and patience

[Micron]

Many queries isn't real efficient either if they are basically the same, but that's not as bad as multiple tables for the same basic thing.
One query with varying criteria might be what you should be doing. Hard to say what that is though, because that code doesn't give any hint that there will be queries coming in to play. It seems it's all about getting a count. Perhaps you should be dealing with one recordset object based on the inputs. You can use DCount with recordsets too.

BTW, do you not have Option Explicit at the top of your code modules? You should (and should turn on the feature in vbe options).

[smaier]

BTW, do you not have Option Explicit at the top of your code modules? You should (and should turn on the feature in vbe options).

It is now . Didn't know it was used outside VB even though it would seem logical due to its relation to VBA, Thank you for the tip!


As far as the problem that lead us to this point, I have figured out how to fix the problem, although I cannot say I have actually figured out the root cause.

What was needed was for me to (as I did when I created the db I posted earlier) convert the linked table to a local table, then copy/paste that converted local table back into the back end. I had also deleted any old rows of data that existed and entered new ones. I did exactly nothing to the tables properties or the controls in the form (other than replacing that combobox with a textbox, but I found that wasn't necessary).

I'm completely baffled. I'm really happy everything works like it should now, but not knowing what the actual reason was kind of bothers me since finding a workaround doesn't really teach me how to avoid the problem to begin with. Perhaps folks more experienced than myself have run into this kind of issue before?

[ssanfu]

Couldn't help myself.... had to modify the code in the dB.

Are there multiple tables the maintenance data is stored in?
"FemcoPMLogData"
"HaasPMLogData"
"HardingePMLogData"
"DoosanPMLogData"
Or maybe they are queries?

Why is the event the "LostFocus" event. I would have used the "AfterUpdate" event.
And I would have the "RunDate" and "Machine" controls (text boxes) UNBOUND.
Because they are bound, the data in the two fields of the table "WorkFormData" changes for each search.

Anyway, here is the code:

Option Compare Database
Option Explicit

Private Sub Machine_LostFocus()
    Dim CheckPMForMachineVar As Variant
    Dim MachineNumberVar As String
    Dim MachineDBVar As String
    Dim MachineVariable As Byte
    Dim ChkCriteriaVar As String
    Dim msg As String

    MachineVariable = Me.Machine

    Select Case MachineVariable
    Case 1 To 3
        MachineDBVar = "FemcoPMLogData"
    Case 4, 9
        MachineDBVar = "HaasPMLogData"
    Case 8, 10
        MachineDBVar = "HardingePMLogData"
    Case 11
        MachineDBVar = "DoosanPMLogData"
    Case Else
        msg = "ERROR!!"
        msg = msg & vbCrLf & vbCrLf
        msg = msg & "Invalid machine number entered/selected!!"
        msg = msg & vbCrLf & "Try again...."
        MsgBox msg
        Me.Machine = Null
        Me.Machine.SetFocus
        Exit Sub
    End Select

    CheckPMForMachineVar = DCount("PerformedOn", MachineDBVar, "[PerformedOn] = #" & Me.RunDate & "# AND " & "[Machine] = " & MachineVariable)

    '************ COMMENT OUT when DEBUGGING complete *********************************
    msg = "MachineVariable -> " & MachineVariable & vbCrLf
    msg = msg & "MachineDBVar -> " & MachineDBVar & vbCrLf
    msg = msg & "CheckPMForMachineVar -> " & CheckPMForMachineVar
    MsgBox msg
    '************ COMMENT OUT when DEBUGGING complete *********************************

    ChkCriteriaVar = "[PerformedOn] = #" & Me.RunDate & "# AND " & "[Machine] = " & MachineVariable
    Debug.Print ChkCriteriaVar

    If CheckPMForMachineVar = 0 Then
        msg = "It looks like Preventative Maintenance has not been performed on this machine today."
        msg = msg & vbCrLf & vbCrLf
        msg = msg & "Please remember to perform machine PMs as soon as possible."
        MsgBox msg
        '    Else
        '        Exit Sub
    End If
End Sub

BTW, in table "WorkFormData", the field name "Function" is a reserved word and shouldn't be used for object names.

[smaier]

Couldn't help myself.... had to modify the code in the dB.

Are there multiple tables the maintenance data is stored in?
"FemcoPMLogData"
"HaasPMLogData"
"HardingePMLogData"
"DoosanPMLogData"
Or maybe they are queries?

Why is the event the "LostFocus" event. I would have used the "AfterUpdate" event.
And I would have the "RunDate" and "Machine" controls (text boxes) UNBOUND.
Because they are bound, the data in the two fields of the table "WorkFormData" changes for each search.

BTW, in table "WorkFormData", the field name "Function" is a reserved word and shouldn't be used for object names.

Hi, ssanfu!

Oh, no worries at all! I welcome any and all suggestions and corrections as long as they are civil. That's why I come here rather than some other sites which can be rather elitist. I like seeing how more skilled/experienced minds than my own create a solution and execute it in proper form. I don't mind being wrong long as I learn something.

I did a bit of programming in Apple Basic when I was in high school back in the late 80's so I get the logic and syntax of coding. However, most of my day-to-day programming these days is in g-code with some ladder-logic sprinkled in here and there. My VBA chops were aquired through self-study trial and error (lots and loooots of error). A couple times a year I'll see a problem I can solve and hack something out in my own mouth-breather knuckle dragging way, but it likely won't be pretty.

Anyway, to address a couple of your questions... the WorkFormData is the db that houses the shop floor's applied labor data (the job they ran, part#, time start, time stop, how many good parts, how much scrap and so forth). The bit of code I was having problems with here was meant to, as they filled out the form for their applied labor data, it would check to see if the machine they used to run the job had had the preventative maintenance performed yet that day and remind them it needs to be done. This is why the RunDate and Machine fields are bound. I did see a problem in that the controls in the form and the named of the columns (fields?) in the table were the same, which I thought was a bad idea and gave them separate names. The FemcoPMLogData/etc objects are tables. One for each type/brand of CNC machine we have as each machine manufacturer has it's own unique maintenance steps. Originally, the applied labor data, preventative maintenance (and other groups of production data) were each in separate db's, and to allow for better in-process control I decided to combine them into a single db.

And THANK YOU for pointing out the word "Function" being a reserved word. I mean in retrospect I should have known better but a couple years ago when I first migrated this whole project away from Excel/Userforms to Access I was not in my comfort zone and it didn't occur to me.

The thing that still has me baffled is what I wound up discovering the needed fix was. I won't quote myself as I think it's bad form, but it's in my post (#22) previous to yours. I just can't seem to make heads nor tails of it. I mean, it worked, but I have no idea why.

My apologies for the wall of text. I avoided it in the beginning as most of the time folks aren't interested in the full scope of the project.



Edit: Oh, and regarding the LostFocus event... it just worked. Not a preference. I have the data entry machines all set up with bar code readers so they can just scan in most of the data points quickly and without errors. And the way I have the bar code readers set up is to terminate the string they send with a line feed, sending the cursor into the next form field set in the tab order. I'll give your suggestion a whirl and see if it still works as intended (which I'm sure it will).

Edit 2, 3, 4, 5: Typos. Oh my, they typos!

Last edit: Should I mark this as solved? I mean, technically things are working now. I just can't determine why the workaround worked.

Thank you all so much!

[Micron]

The FemcoPMLogData/etc objects are tables. One for each type/brand of CNC machine we have

Personally, as alluded to in posts 18 and 21 (?) I think you should have 1 equipment table, and the maintenance particulars don't belong in there. That approach is too much like Excel, which makes a poor model for a relational database. I come from a maintenance world and did a fair bit of db design related to maintenance. I only make that point so that you don't take that assessment as just some Access dweeb preaching database dogma.
Back to the WS.

[smaier]

Personally, as alluded to in posts 18 and 21 (?) I think you should have 1 equipment table, and the maintenance particulars don't belong in there. That approach is too much like Excel, which makes a poor model for a relational database. I come from a maintenance world and did a fair bit of db design related to maintenance. I only make that point so that you don't take that assessment as just some Access dweeb preaching database dogma.
Back to the WS.

Hi, Micron!

I totally agree with you. Currently my approach is a flat db since it's easy to understand. As I work with Access (as compared to my original introduction to "databasing" with Excel) I am slowly getting my head to think that way. It's more a lack of confidence on my part rather than thinking my approach is better. Until I feel my confidence is deserved I err on the side of caution.

Baby steps for this man

[Micron]

Currently my approach is a flat db since it's easy to understand

The problem with that approach is that it typically introduces issues. Sometimes they can be solved, sometimes not, and sometimes only with great difficulty. Learning and understanding db normalization are 'baby steps'. You might be trying to run a relay race before learning to toddle.

[CJ_London]

It is not that difficult if you use the tools available

There three rules to bear in mind

• don't repeat data. The objective is to store a piece of data (such as a product name, a customer name) only once. There are a couple of possible exceptions around 'legal' documents (those that are required for the outside world such as sales invoices where you will want to be able to reprint an invoice a year down the line exactly the same as when it was originally printed - so you store data such as price and tax rates) , but that is the principle
• each table represents a collection of entities (customer, supplier, product, order header, order line, etc) and needs a unique ID (a primary key), usually an auto number, but might be a text abbreviation (for US states or currencies for example)
• data of the same type/profile/entity should appear in one table 'people/contacts', 'products/subassemblies'.

but rules are made to be broken. For examples customers and suppliers are both companies (same entity) - they could be in the same table but their purpose for being there is different. One buys product, the other sells it. So although the structure for each will be the same, you would (or could) have separate tables because of the way that entity interacts with the other data.

And there are only 3 types of relationships 1-1, 1-many and many - many. 1-1 is rarely required (usually to do with data security/privacy), 1 - many is common (one customer, many invoices, one product many invoice lines) as is many to many (one customer buys many products, one product is sold to many customers). In this situation you need a joining table which sometimes is what I would call a 'natural' joining table - in this example it would be the invoice header table. But sometimes you need a specific joining table which simply joins two tables together.

The other tip is to use meaningful field names in the context of the whole app, not just the table and don't duplicate them - don't just use ID, use customerID, supplierID or better customerPK/FK which also tells you which end of a join a field is situated. Similarly use customerName rather than just Name (which is a reserved word anyway). Also avoid spaces and non alpha numeric characters - this will make query building and coding easier. Look at using the field caption property if you want 'customer name' to appear when a form or report is created although it only takes a moment or two to edit it.

To visualise this, in the good old days you could use post it notes, one for each piece of data and stick it on a wall under a heading such as customer, invoice, product, then use a bit of string to represent the joins.

But there is another way - try using the relationships window to construct your database which you can use instead of post it notes and a wall

create some empty (for a new app) tables with the fields you are confident are in the right place (tblCustomers with customerPK and customerName fields for example)

Now open the relationships window and drag these tables onto the window.

Connect the tables on PK to FK (an invoice header table will need a customerFK to link to the customerPK in the customers table) and try to set the layout to represent the flow of relationships.

As you need to add more fields, in the relationships window right click on the table header and select Table Design. This will open the table and you can add your field there, then close it. The relationships window will be immediately updated. Same applies if you want to rename a field, remove it, change the order of them, whatever.

If you need a new table, you will need to go back to the design tab to create it but once done, all editing can be done from the relationships window

Using this method, you can see all the tables, all the field names and you can trace a path via the joins as to how you get from one table to another. Note there should only be one path, if you have a circular path, that usually implies there is something wrong with your design.