Solved Follow on VBA question

[MediaHolic]

Trying to limit the code to only check when '6' "Top 10 most wanted" is selected from the drop down option. As it currently checks for all items 1 through 6 if they are selected.

OwnStatus being the name of the combo box on the form.

Private Sub OwnStatus_Click()

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

End Sub


Private Sub OwnStatus_Click()

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

End Sub

Tried referencing the drop down by position and text but still getting errors. Any ideas?

 

[MediaHolic]

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.

Thank you very much, greatly appreciated.

 

[Mark_]

Is "OwnStatus" text? If so then try If OwnStatus = "6" Then

 

[Gasman]

You test for 6 in one place and '6' in another?
Hence getting the correct data type to start with.
Learn to walk your code with F8 and breakpoints.

 

[MediaHolic]

Is "OwnStatus" text? If so then try If OwnStatus = "6" Then

It is, I have tried that and this appears!

 

[MediaHolic]

You test for 6 in one place and '6' in another?
Hence getting the correct data type to start with.
Learn to walk your code with F8 and breakpoints.

Yes, I've tried both in the code as the item number and the value.
New to me! Never used breakpoints, presumably f8 will execute code consecutively?

 

[MediaHolic]

Kind of getting there!

Private Sub OwnStatus_Click()

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

End Sub

 

[Gasman]

Where is the If and End If for the DCount()

 

[theDBguy]

Kind of getting there!

Private Sub OwnStatus_Click()

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

End Sub

Try:

If OwnStatus="Top 10 most wanted" AND DCount("*","tbl_Smithsonian","[Own]=6")>=10 Then

 

[Mark_]

Please remove the space between > and =.

 

[MediaHolic]

Where is the If and End If for the DCount()

Couldn't get it to work. This compiles, error free but the message box no longer appears.
Private Sub OwnStatus_Click()

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

End Sub

 

[MediaHolic]

Please remove the space between > and =.

Done, thank you.

 

[MediaHolic]

Try:

If OwnStatus="Top 10 most wanted" AND DCount("*","tbl_Smithsonian","[Own]=6")>=10 Then

Perfect! Thank you so much.

 

[theDBguy]

Perfect! Thank you so much.

Glad to hear you got that part sorted out. Good luck with your project.

 

[MediaHolic]

Glad to hear you got that part sorted out. Good luck with your project.

As an aside, I've set it to select no as the owned status afterwards. This wasn't what was originally intended but it's better than what I first wanted.

Private Sub OwnStatus_Click()

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

End Sub

 

[Gasman]

Couldn't get it to work. This compiles, error free but the message box no longer appears.
Private Sub OwnStatus_Click()

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

End Sub

You have two If and one End If.
You either make two separate statements or combine them as theDBGuy did.

Again, if you walked your code, you would see what it actually does, not what you think it does.

 

[MediaHolic]

You have two If and one End If.
You either make two separate statements or combine them as theDBGuy did.

Again, if you walked your code, you would see what it actually does, not what you think it does.

I'll have to find out how to add breakpoints and do walkthroughs. I appreciate the advice, thanks again for your help.

 

[Gasman]

Try using the debugging link in my signature.

 

[June7]

Sorry, my goof on the [ID Own?] field name.

ID should be a number type, even if not autonumber. Then the related foreign key field also needs to be a number type.

Also, still recommend BeforeUpdate event. That's customary for data validation.
https://learn.microsoft.com/en-us/office/vba/api/access.combobox.beforeupdate-event
You are using Click event which won't trigger if someone tabs into control and starts typing.

 

[MediaHolic]

Try using the debugging link in my signature.

Thank you.