Solved VBA form issues from a newcomer

[MediaHolic]

Hello,

I'm new to the forum and as my name suggests, I'm creating an access database (Access 2019) to track all my blu rays.

Complete novice to VBA but have a history in C++ years ago so the construct is familiar but trying my best every day and making baby step progress.

My problem is, I have a single form (attached image, first draft, please be kind) that has a drop down list of items regarding the purchase state of the movie, if it is owned (options are "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale") as a drop down list, Yes is highlighted. On the line below, the second item in is most wanted for my top ten wants, included as a check box. If the most wanted is selected and if the owned state is currently No and is then changed to Yes as I've acquired the item, then it can no longer be in the top ten wanted.

So, how to I check if "most wanted?" check box is populated, deselect it if No is changed to Yes and then disable the most wanted box completely as it cannot be wanted if I now own it?

This was my attempt but it doesn't work:

Me!premiuml.disabled = (Me![Own?].ownedl="Yes")

Most wanted check box is called premiuml
The drop down list is called ownedl

Any help or advice would be greatly appreciated. Thanks in advance.

 

[MediaHolic]

The control source for the dropdown is [Own?]

 

[GPGeorge]

Hello,

I'm new to the forum and as my name suggests, I'm creating an access database (Access 2019) to track all my blu rays.

Complete novice to VBA but have a history in C++ years ago so the construct is familiar but trying my best every day and making baby step progress.

My problem is, I have a single form (attached image, first draft, please be kind) that has a drop down list of items regarding the purchase state of the movie, if it is owned (options are "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale") as a drop down list, Yes is highlighted. On the line below, the second item in is most wanted for my top ten wants, included as a check box. If the most wanted is selected and if the owned state is currently No and is then changed to Yes as I've acquired the item, then it can no longer be in the top ten wanted.

So, how to I check if "most wanted?" check box is populated, deselect it if No is changed to Yes and then disable the most wanted box completely as it cannot be wanted if I now own it?

This was my attempt but it doesn't work:

Me!premiuml.disabled = (Me![Own?].ownedl="Yes")

Most wanted check box is called premiuml
The drop down list is called ownedl

Any help or advice would be greatly appreciated. Thanks in advance.

Let's start with the tables involved. Show us, please, the relationship diagram for the tables.

 

[GPGeorge]

Let's start with the tables involved. Show us, please, the relationship diagram for the tables.

I also strongly urge you to revise the naming convention. Characters like the question mark are problematic in field and table names because they have other uses, such as in wildcards. It makes things tricky to have nonalpha characters in object names.

 

[MediaHolic]

Thank you, I'm not too familiar with the syntax and wildcards, I'll amend this once I get the code working.

Relationships as requested, see attached and thank you.

 

[The_Doc_Man]

Just to amplify GPGeorge's comment, we have the best results when we can see what is on the playing field.

As an overview, if I read this right, you can list media that you do not own. When the choice is made as "Owned" (from a combo box) then you have to click it to change the selected option. You can take action to disable the WANTED check box in that box's OnClick event. However, you would have that same action in the form's OnCurrent event (which triggers after a Form Save action OR a Form Navigate action).

Here's another question... if OWNED is a possibility, what are the other options in that box?

My own media DB is a little more extensive. Every one of the drop-downs is fed by a table of options. For instance, "Format" can be standard (VGA equivalent), Blue-Ray, or UHD/4K. I have maybe 30 or so "Genre" possibilities. One other difference is that if I don't own it, I don't list it. The "Person and Role" box is a sub-form with two columns. I have something on the order of 300+ video disks listed this way, though when I started I didn't have something for "Persons and Roles" - that was an add-on.

 

[MediaHolic]

Hello Doc, thank you for the reply.

The main table is a list of all the items that I am aware of, ordered by film appearance. If I own it or not, the respective option is selected. I run a query for films I have yet to own.

This form is the only form for the smithsonian table, selected fields are included for this field only as later on, when I have a better understanding I'll do sub forms to meet my requirements.

As listed in my original post, the available options are as follows:

options are "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale"

Task of the moment is to be able to clear and disable the most wanted check box if owned is yes.

Thanks again.

 

[GPGeorge]

Thank you, I'm not too familiar with the syntax and wildcards, I'll amend this once I get the code working.

Relationships as requested, see attached and thank you.

Like The_Doc_Man, I have a database of books and one of albums--yes, old-fashioned vinyl. But that's somewhat beside the point, other than that I also list only items I own.

Your application apparently serves a different purpose. It appears to track "items of interest", rather than "items owned". Otherwise, a lot of similarities.

I see this comment, "...if it is owned (options are "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale") "

I think this list should be: "Currently Owned", "Previously Owned", "Not of Interest", "Yet to be Released", "On Order" and "Never seen for sale"
I assume that you may eventually dispose of an item at some point, so that should be an option. I also assume that you either own an item, plan to own it, or never plan to own it, but just list it for completeness, hence, "Not of Interest" although there may be a better choice of words for that. I also am not sure "Yet to be Released" fits this category because it may or may not be something you want to own whether released or not. I might actually make that a different attribute. In fact, what I would do is create a field called "ReleaseDate" which indicates when the item was released. If there is no date in that field, it's not yet released. Then that anomalous term can be taken out of the list.

I think you should eliminate the table called "tblOwned" because it is redundant.

Also, the relationship diagram shows NO enforcement of Referential Integrity. Invest some time learning about that and how important it is to data integrity in a database.

 

[GPGeorge]

Hello Doc, thank you for the reply.

The main table is a list of all the items that I am aware of, ordered by film appearance. If I own it or not, the respective option is selected. I run a query for films I have yet to own.

This form is the only form for the smithsonian table, selected fields are included for this field only as later on, when I have a better understanding I'll do sub forms to meet my requirements.

As listed in my original post, the available options are as follows:

options are "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale"

Task of the moment is to be able to clear and disable the most wanted check box if owned is yes.

Thanks again.

You're ahead of yourself working on forms. You need to sort out good table design first.

 

[MediaHolic]

Hello George,

Thank you for the comments.

Regarding the items of interest, I'm only including items I would like then once owned, update the status accordingly. I don't sell items and if it wasn't of interest, I wouldn't include it in the table as it would be redundant and taking up space for something I would like, it avoids clutter in my logic. I thought about release date but these films go back sixteen years. It's easy to track the release date of the film but as media is included from all over the world, getting an accurate release date for each blu ray would be a fool's errand.

I'll research referential integrity, as I said I'm still new to this. I appreciate the input.

 

[MediaHolic]

Found a problem and corrected it:

Most wanted changed to Top10

Me!Top10.disabled = (Me.[ownedl].Value = "Yes")

So this should disable the check box in the form if Yes is selected from the ownedl drop down, but it doesn't.

 

[MajP]

No such property called "disabled". There is an "enabled" property of a control. Maybe that is a typo, but if not surprised you could compile it.

 

[MediaHolic]

Thank you, that explains this!

 

[GPGeorge]

Hello George,

Thank you for the comments.

Regarding the items of interest, I'm only including items I would like then once owned, update the status accordingly. I don't sell items and if it wasn't of interest, I wouldn't include it in the table as it would be redundant and taking up space for something I would like, it avoids clutter in my logic. I thought about release date but these films go back sixteen years. It's easy to track the release date of the film but as media is included from all over the world, getting an accurate release date for each blu ray would be a fool's errand.

I'll research referential integrity, as I said I'm still new to this. I appreciate the input.

That sounds logical, thanks. I understand you do not sell items, but is it possible one could get up and walk away some day?

 

[MediaHolic]

This is true or get damaged, in which case I would try to find a replacement.

I thought it best to concentrate and catalogue what I wanted as opposed to everything I come across, some of which I have no intention of owning. The standard blu rays in the amaray cases are two a penny but one that is released with a pop up sleeve (such as the latest deadpool & wolverine blu ray) or contains cards or a poster etc., is worth collecting.

 

[MediaHolic]

Interestingly enough, this locks the check box:

Private Sub ownedl_Click()

Me!Top10.Locked = (Me.[ownedl].Value = "1")

End Sub

I have a table with the options "Yes", "No", "On Order", "Yet to be released" and "Never seen for sale" with Yes being 1 etc.

So if No is selected for owned and the wanted box is checked, selecting Yes for owned locks the cell but doesn't clear the wanted check box. Is this possible?

 

[MajP]

Public Sub LockAndClear

    Me!Top10.Locked = (Me.[ownedl].Value = 1)
    if me.ownedl.value = 1 then Me.Top10.value = 0
    'I assume that is a yes no field
end sub

I assume you want to call this method on the forms oncurrent and ownedl_click. This will unlock other records where ownedl <> 1 when moving between records.
I assume 1 is a real numeric and not a string. No need for ""

 

[MediaHolic]

I assume you want to call this method on the forms oncurrent and ownedl_click. This will unlock other records where ownedl <> 1 when moving between records.
I assume 1 is a real numeric and not a string. No need for ""

Yes and this works perfectly! Thank you for your help.

Changed owned to no and clicked the wanted box, changed owned to yes and it clears the wanted box and prevent it from being selected.

Can the wanted box be greyed out or is that not possible? Sorry if I'm being a pain.

Thank you again for your help.

 

[Pat Hartman]

Since "top 10" is mutually exclusive with "owned", why would you not just add it as a category in the combo?

 

[The_Doc_Man]

Pat beat me to it. "Most wanted" or "Top 10 Wanted" should just be another status that would change according whether you ordered it yet. Then you don't need the checkbox at all.