Solved Follow on VBA question

[MediaHolic]

As per my previous post: 1943462

Quick summary, I have a database of steelbooks and have incorporated a most wanted tick box into a drop down menu added as the last item.

As suggested by a member, the check box was obsolete and should be incorporated into the list:

tbl_own

ID Own?
1 Yes
2 No
3 On order
4 Yet to be released
5 Never seen for sale
6 Top 10 most wanted



The last item, item 6 has now replaced the most wanted check box. This populates the main table with the number 6 against the film entry.

There is main form, details of which are included in the link.

My question is, how would VBA check that no more that 10 items have been selected for the top 10 and if so, prevent selection from the drop down list or display a message so no more than ten items have 6 set in the own field?

Please keep in mind I'm still new to this and data integrity etc. are being revised, I'm simply interested in the task at hand. Thank you all in advance.

As an example, if I have 100 films and ten are already selected at top 10 (item 6) then another one cannot have top 10 (item 6) selected)

 

[Mark_]

Step 1 for me would be to make a small query that returns all "Top 10 selected". The query would only have the minimum fields needed to return the "Top 10" for a given user (or artist, or what ever is appropriate), so probably the field in question AND the parent this is related to.

Once you have the query, press the "Totals" button on your query design ribbon and group by the parent. Then in the "Total:" entry, Count the "Top 10 selected".

You can then use DLookup on this query to return the number of "Top 10 Selected" for the parent record.

If this doesn't answer your question, please post your tables and their relationships.

 

[MediaHolic]

Thank you for the reply. I'm trying to teach myself VBA so interrogate the own field for ten or fewer of item 6 then either disable or display a message that ten have already been selected.

 

[Mark_]

You can still create the same query, it just is on "Own".
You will want to also learn a bit more about data structures, naming conventions, and data normalization. You do not want spaces or special characters (such as "?", "/", or "-") in field or table names.

Start with setting up a query on your Tbl_Smithsonian. Only field you need is "Own". Set it up as a "Total" query on "Own". Then begin working on setting up the where to only return records that match what you are looking for.

 

[June7]

These 6 items are mutually exclusive?

Don't need a query. DCount will do the job.

If DCount("*", "tbl_own", "[ID Own?]=6") = 10 Then
'do something
End If

The real trick is figuring out what event to put code into. Perhaps [ID Own?] combobox before update event.

Strongly advise not to use spaces nor special characters (underscore only exception) in naming convention

 

[Mark_]

June7, I had a brain fart and forgot about DCount. I'm blaming not having had coffee yet!

 

[MediaHolic]

These 6 items are mutually exclusive?

Don't need a query. DCount will do the job.

If DCount("*", "tbl_own", "[ID Own?]=6") = 10 Then
'do something
End If

The real trick is figuring out what event to put code into. Perhaps [ID Own?] combobox before update event.

Strongly advise not to use spaces nor special characters (underscore only exception) in naming convention

Yes the 6 are. When I add an item that I already have or have seen and select the status of 1 through 6 depending upon availability/want.

I was thinking about the code as a click event on the own drop down list to query the total count, is this not the way to do it?

Yes, I'm still learning regarding spaces, characters etc. and making amendments as I learn. This is more a proof of concept as was my last post to learn what I can do, what shouldn't be done and hopefully think of new things to do.

I'll mess with the code and let you know, thank you.

 

[Mark_]

Thinking about it, you will want to check if you have 10 OR MORE already in. That way you can catch when you have bad data to begin with that you don't let MORE bad data get in.

 

[MediaHolic]

I was thinking about >= 10 but I can't get the code to work.

Table is smithsonian (tbl_smithsonian) and the column is called own, check to see if there are ten or more in the own column of item 6 (set as text not a number) of the smithsonian table:

Private Sub OwnStatus_Click()

If DCount("*", "tbl_Smithsonian", "[ID Own]=6")> = 10 Then
MsgBox "Ten most wanted already assigned"
End If

End Sub

 

[Gasman]

If the column is called Own, why are you using [ID Own]?

 

[MediaHolic]

It was suggested in the example I was given, I take it that it isn't needed? Just [Own]?

 

[MediaHolic]

Private Sub OwnStatus_Click()

If DCount("*", "tbl_Smithsonian", "[Own]=6") >= 10 Then
MsgBox "Ten most wanted already assigned"
End If

End Sub

Gives this error now.

 

[MediaHolic]

It works! Encased the 6 in single quotes being a text value not a numeric.

Private Sub OwnStatus_Click()

If DCount("*", "tbl_Smithsonian3", "[Own]='6'") >= 10 Then
MsgBox "Ten most wanted already assigned"
End If

End Sub

Now, can you limit the check to only execute if you select item 6 "top 10 most wanted" from the drop down menu as it currently checks for all items 1 through 6 if they are selected?

 

[Mark_]

In OWN, are you saving the number (ID) from the Tbl_Own OR are you saving the text returned from tbl_own.Own?
Former is saving a reference, later is saving the value.

Easy way to check is look at how you've declared OWN. If it is numeric, you should be saving the ID from tbl_own. If it is text, you could be doing either. I am guessing you are using a text field to hold this value.

If you are actually using a text field to save the ID, then you would need to treat your criteria as a string comparison rather than straight numeric. Instead of "[Own]=6" it would be "[Own]='6'"

 

[Gasman]

It works! Encased the 6 in single quotes being a text value not a numeric.

Private Sub OwnStatus_Click()

If DCount("*", "tbl_Smithsonian3", "[Own]='6'") >= 10 Then
MsgBox "Ten most wanted already assigned"
End If

End Sub

Now, can you limit the check to only execute if you select item 6 "top 10 most wanted" from the drop down menu as it currently checks for all items 1 through 6 if they are selected?

Really an ID should be numeric.

 

[MediaHolic]

In OWN, are you saving the number (ID) from the Tbl_Own OR are you saving the text returned from tbl_own.Own?
Former is saving a reference, later is saving the value.

Easy way to check is look at how you've declared OWN. If it is numeric, you should be saving the ID from tbl_own. If it is text, you could be doing either. I am guessing you are using a text field to hold this value.

If you are actually using a text field to save the ID, then you would need to treat your criteria as a string comparison rather than straight numeric. Instead of "[Own]=6" it would be "[Own]='6'"

Looks like our replies crossed, thank you. I forgot about enclosing the 6 in single quotes as the drop down list populates the number in text on the table. It was saving the value, as you rightly pointed out.

 

[MediaHolic]

Really an ID should be numeric.

Thank you for the suggestion. I'm still learning as I thought my hobby would be a great Segway into access and vba, you learn quicker if it holds your interest and I'm in the early stages experimentation. A year from now this will be a far cry from what it is now (hopefully) thanks to each learning experience.

 

[Gasman]

You are going confuse most of us here in that case. I would say we would expect any ID, Pk or FK to be Long. So best state the types for further posts, or correct it now whilst easy to change.?

 

[Mark_]

To get the ID number but return the text, set up a query with TWO fields, the ID and the Value. In the drop down you use your source query and have the first field bound. The display settings would be 0; <number big enough to show your field> so that the ID itself is hidden. These are TABLE and FORM questions though, as you can do this without going into VBA to set this up.

 

[MediaHolic]

You going confuse most of us here. I would say we would expect any ID, Pk or FK to be Long. So best state the types for further posts, or correct it now whilst easy to change.?

I'll specify the type until I make the adjustments. Thank you.