seeking ideas how to change record state

[mysterj1]

I have a small db Access 2003 db that I've created for tracking my home music collection. At present, I use 2 yes/no fields to track the location of the music: iTunes (yes/no) and Cold_Storage (yes/no). Due to disk space consideration, I periodically move some of the music to an external disk (hence the cold storage name). If the iTunes field is checked (yes), then the Cold_Storage field is not (no). What I would like to do is to have a means to automate my form so that I can open a record and with one click have it change the location (in the db; I am still going to manually move the files). It would probaby also be a good idea to make sure that if the album is in iTunes, it is not in cold_storage, and vice versa.

Is there a easy way to do this with a form? The form name is Edit Album Information, the table name is Albums.

Thanks for your expertise!

Guiseppe

 

[mysterj1]

I've considered having a 2nd table for the cold storage albums, but since I may likely move the songs back in time, it's easier to leave it all in one table.

 

[pbaldy]

Here's an option you might consider, since it can only be in one place. You can use a single numeric field with an option group to select the value. For instance, 1 could be iTunes and 2 cold storage.

If you want to stick with your 2 fields, code like this in the after update event of each checkbox:

Me.OtherCheckboxName = Not Me.CheckboxName

The "Not" will reverse the value, so checking one will uncheck the other, and vice versa.

 

[mysterj1]

I like your idea about the option group, but since I'm semi-committed I'll update the check boxes. If I am reading your reply correctly I'd do it like this? (check boxes are also named iTunes and cold_storage)

I'd change the After Update property for the iTunes check box to:
Me.Cold_Storage = Not Me.iTunes

and the Cold_Storage check box after update property to:
Me.iTunes = Not Me.Cold_Storage

 

[pbaldy]

Looks like what I had in mind. That is VBA code, so it wouldn't go in the property directly, it would go here:

http://www.baldyweb.com/FirstVBA.htm

 

[mysterj1]

Followup:

is Me the table name, or the form name? I'm seeing now that the spaces in the form name aren't the best idea.

 

[pbaldy]

Leave "Me." as it is; it's a shortcut to refer to the object containing the code, in this case your form.

 

[pbaldy]

Oh, and I completely agree with your thoughts on spaces. They aren't worth the bother in the long run.

 

[pbaldy]

I'd use the after update event, but that should work. Have you tried it?

 

[mysterj1]

Hmm. I think it seems like either after update or on click ought to work. But...

after entering the code, it doesn't respond! (????)

I have saved the code after entering it.

Private Sub Cold_Storage_AfterUpdate()
Me.Cold_Storage = Not Me.iTunes
End Sub

Private Sub iTunes_AfterUpdate()
Me.iTunes = Not Me.Cold_Storage
End Sub

Scratching my head.

 

[mysterj1]

Followup:

on a record I have with the itunes check box selected, when I click the cold storage check box it doesn't "check" it OR deselect the itunes check box.

When I click the itunes check box it doesn't uncheck that one.

Hope that makes sense.

 

[mysterj1]

See attached.

 

[mysterj1]

Hmmm. I took a few minutes to read up on the VBA window and its components, and since I wasn't having any luck w/ previous attempts I went ahead and cleared my previous work and went with your suggestions via coding straight into the editor. And it worked, first time.

No idea where I was going wrong, since I was in essence adding the same mechanism. But it worked

Also learned how to debug, at least a bit. So, attaboy to you, long story short I learned at least the environment for knocking out some code, and now my curiousity is working

Thanks again,
Guiseppe

 

[pbaldy]

Glad you got it sorted out Guiseppe, and all while I slept.

 

[gemma-the-husky]

out of interest, paul's idea of having a single field to store the data location is a better way to go, eventually

eg - what happens if you introduce some more storage alternatives. you would have to keep changing the database, by adding more tick boxes. doing it Pauls' way, you simple select a different value from the drop down.

 

[mysterj1]

out of interest, paul's idea of having a single field to store the data location is a better way to go, eventually

eg - what happens if you introduce some more storage alternatives. you would have to keep changing the database, by adding more tick boxes. doing it Pauls' way, you simple select a different value from the drop down.

Gemma, I know that check boxes are not the preferred way and may very well change those two fields to one utilizing an option group in the future. This works ok for the time being as I'm only going to have my tunes store in one or the other. Great input though