Solved VBA/Form syntax

[CosmaL]

Dear friends,

i have the following code, in a save button:

Private Sub cmdSave_Click()

Dim lcount As Integer

If Me.Dirty Then Me.Dirty = False

lcount = DCount("casnumber", "tblCLPDGRPictogrammes", "[casnumber]=" & Me.CASNumber & " And [completed]='Yes'")

If lcount <> 0 Then
Me.Text1134 = "Completed"
Else
Me.Text1134 = "Pending"
End If

I need to look in a table's data (2 fields) and update a txtbox in my main form.

I always get "Pending" even if both parameters contain the appropriate data.

Any ideas?

Thank you in advance!!!

Costas

 

[Gasman]

Is Completed a Boolean field?, in which case either use Completed = True or just Completed.
If you walk though your code, you should see that lcount is ALWAYS 0, which infers the DCount is incorrect?

 

[plog]

Debugging 101--find out what is actually in your strings/variables.

Obviously lcount is always 0. Which means your DCount always returns 0. Since its not throwing an error the first two arguments are correct (FieldName and TableName) and any fields you use in the criteria are also valid. So, that leaves only 1 culprit, the criteria as a whole is excluding everything.

Build that criteria string in a variable and spit out the variable so you can see with your actual eyes what criteria it is passing in the DCount. Then make sure that's the right criteria.

Additionally, and maybe not unrelated--'Yes' is a very weird value to store in a field. There are Yes/No data types which you should use for boolean values and those fields do not store the literal value 'Yes', they store numbers.

 

[CosmaL]

[Completed] is a textbox which you have to write "yes" or "no"

I will change it to checkbox and follow your directions!

Thanks!!!!!!

 

[Gasman]

[Completed] is a textbox which you have to write "yes" or "no"

I will change it to checkbox and follow your directions!

Thanks!!!!!!

No, no need. Just that I would have had it as a boolean field.

Show us pics of records that *should* satisfy that DCount.
You might want to give your controls more meaningful names as well? Text1134 does not tell anyone anything, other than you have created and deleted a lot of controls on that form?

Even create a simple query to show those records?

 

[CosmaL]

No, no need. Just that I would have had it as a boolean field.

Show us pics of records that *should* satisfy that DCount.
You might want to give your controls more meaningful names as well? Text1134 does not tell anyone anything, other than you have created and deleted a lot of controls on that form?

Even create a simple query to show those records?

I've catched dcount with msgbox and always get 0, with or without data.
It seems that it doesn't read anything in the table.

Table's structure contains:
CASNumber: short text
Completed field: short text (accepted values as text, Yes/No)

Running the query, i get only the records containing data in CASnumber and completed fields.
Casnumber is also the recordkey. Greek ναι is Yes

Query1

CASNumber	Completed
7772-98-7	Ναι
1310-73-2	Ναι

On the main form, *textbox1134 should display-after the record is saved- "Completed or not completed" based on dcount.
example on table:
CAS: 1310-73-2, completed: Yes
After dcount, text1134 should have been "Completed"

I think i'm missing something in dcount.

*I should have changed the name on the beggining, now im stuck with dcount and rename will be a little bit delayed

 

[Gasman]

Well you are classing CasNumber as a number in your DCount
I am surprised it did not give you a Type Mismatch.

So surround Me.CASNumber with single quotes as you did for Completed.

You are the second person today, who enclosed one control value correctly and then did not do the same for the same type of control value?

 

[CosmaL]

Well you are classing CasNumber as a number in your DCount
I am surprised it did not give you a Type Mismatch.

So surround Me.CASNumber with single quotes as you did for Completed.

You are the second person today, who enclosed one control value correctly and then did not do the same for the same type of control value?

I really didn't notice it

Thanks again!!!!!

 

[Pat Hartman]

1. You are saving the record BEFORE assigning the Completed or Pending value. That can't be right.
2. You didn't bother to give your control a meaningful name so who knows what "Text1134" means??
3. The fact that you have gotten to a value of 1134 for a control name is pretty scary. You also are not compacting enough.
4. And finally, there is a lifetime limit of the number of controls that can be added to any form over its entire life. So if you put a form in design view and add 4 controls. then delete those controls and add 4 more, you have now used EIGHT of the maximum number of controls this form can EVER hold.

 

[CosmaL]

1. You are saving the record BEFORE assigning the Completed or Pending value. That can't be right.
2. You didn't bother to give your control a meaningful name so who knows what "Text1134" means??
3. The fact that you have gotten to a value of 1134 for a control name is pretty scary. You also are not compacting enough.
4. And finally, there is a lifetime limit of the number of controls that can be added to any form over its entire life. So if you put a form in design view and add 4 controls. then delete those controls and add 4 more, you have now used EIGHT of the maximum number of controls this form can EVER hold.

1. Corrected, thanks!
2. Control name already changed
3-4. It's a test database with a lot of forms/controls, and a lot of additions/deletes.

Thanks again!

 

[Pat Hartman]

It's a test database with a lot of forms/controls, and a lot of additions/deletes.

Neatness counts. Unless you plan on completely rewriting the application from the ground up once you like how it works, what is your plan for cleaning up the mess you are leaving behind? Clean up as you move along. It is poor practice to leave the "kitchen" a mess while you are cooking. It leaves you in control and better positioned to move on with confidence that you won't need to go back.

 

[CosmaL]

Thanks, I'm thinking of rewriting the application.
I'll keep your suggestion for my new apps!

 

[Pat Hartman]

The sign on the wall in one of my early managers' offices.

If you don't have time to do it right, what makes you think you have time to do it twice.

Words to live by and I took them to heart.