Using a command button to change state of a value in a field

[atcguard86]

First, I'm an extreme novice in Access(2007-2010). I'm attempting to build a few listboxes that display items based on their state, and allows them to move from one to the next by way of command buttons. I've been trying to copy and paste snippets of other peoples example code to make this work but I'm not having a ton of success.

As it stands now I've been able to build the 3 listboxes and get them to populate correctly based upon the status of a field called "AA_Status" with 3 states: "Not Covered", "Training" and "Complete" from a table titled "STS".

I've got 3 listboxes named "ListAA_NotCovered" "ListAA_Training" and "ListAA_Complete" that I'd like the items to display between based on the corresponding states above. I also have multi-select "enhanced" enabled, which as far as I can tell requires some sort of integer based logic that I can't figure out.

I've also attached a depiction of what I'm trying to accomplish as that may make it a little easier for you to understand what I'm trying to accomplish.

If anyone can show me the necessary code to enable just one of the buttons to function I should be able to infer how the rest need to be formatted.

I truly appreciate any help, advice, or feedback you can provide. This really isn't my area of expertise at all, I'm an Air Traffic Controller by trade and I'm attempting to streamline some of our training processes through some slick MS Access forms.

-ES

[NeoPa]

Hi. I like your approach.

What basic data are you working with?

Logic is fair enough, but without an understanding of the data in use it's without a referential basis. Three times something that isn't defined is simply something else that isn't defined.

A pretty good first stab - but we'll need that understanding in order to appreciate what we're talking about.

[atcguard86]

Thank you, I had hoped to explain myself better but couldn't seem to do it without writing a novel but I'll try to keep it concise.

I may have used poor terminology saying that I needed to use integer base logic. That was in reference to coding I'd seen for handling the selection of multiple items in the list box. For example:

Dim i As Integer = Listbox1.SelectedIndex

If i = -1 Then

I honestly don't know what that is intended to do, but it seemed to accomplish something similar to what I am hoping to do.

So the basic data I'm working with is a table with a field for an STS number (a discrete identifier), a corresponding title, a field to define which block of training it will be active in, and then a field that defines what state it's in when active ("not covered", "training" or "complete").

I'm using three listboxes on a form to display the STS numbers and titles in the box that corresponds to their appropriate state. I also would like buttons that would (for the user) move the STS item from one list box to the next. However, on the backend I'd like it to "move" by changing the state from one to the other. (I've already got my list boxes set up to populate based upon the appropriate block of training, and then by its state, so they are functioning how I'd like them to)

I could just use radio boxes for each STS item to display their status, but there will be approximately 40 different items and I'd like to avoid having that many items to click through, I also think it would be more aesthetically pleasing this way, particularly when I eventually print the completed forms.

Once I get this functionality accomplished I plan to pull from a personnel database to pre populate the rest of my form with trainee and trainer information, and also pre populate dates based upon a start date and defined deadlines, I'd also like to build a database that stores text from a narrative and ties it to the trainees discrete identifier. But those are all plans for down the road, I need to clear this hurdle first.

I hope I gave you enough information and a better idea of what I'm hoping to accomplish.

[atcguard86]

Sorry to double post, but I've been trying to read and learn some more while I wait, and I think I've made some progress. Perhaps someone can nudge me the rest of the way to where I'm trying to get with this code.
(I'm sure the error stands out like a sore thumb to you guys, but I'll describe it anyways)

On line 6 it states "Compile Error: Method or data member not found" obviously I have no idea what that means, but I'm sure one of you does.

Private Sub cmd_ToTraining_Click()
 
    Dim x
    Dim values
 
    For x = 0 To list_NotCovered.length - 1
        If list_NotCovered.ItemData(x).Selected Then
            Update t_STS.AA_Status = "Training"
        End If
    Next
 
 
End Sub

[PhilOfWalton]

It might pay you to have a look at https://bytes.com/topic/access/answe...toolbar-dialog

Phil

[jforbes]

I not so sure that ListBoxes are the best fit for what you are trying to accomplish. I think you should consider using three SubForms, where the SubForms are a DatasheetView of your Training Table. There are a couple reasons for this.

One is that it allows Access to do things the way it does them best. It doesn't have a whole lot going on when it comes to ListBoxes. It's not bad, but often a DatasheetView SubForm can perform a similar function and provide additional functionality, usually with less code. There is some pretty slick ways you can link a MainForm and SubForm together to replace the code that would need to be created for ListBoxes.

The Second is that you mention incorporating a Personnel database to populate a Trainer and Trainee. So, I'm guessing you are going to want a one to many relationship between each Trainee and every Course as well as a some kind of relationship between Trainee and Trainer and probably through the Course. This is the start of a somewhat complicated relationship that you may want to nail down before going further.

Honestly, this last part concerns me the most as you may find all the hard work you put into your database is wasted.

[atcguard86]

PhilofWalton, thank you so much for that link, that looks almost exactly like what I'm trying to accomplish! I'm going to sit here for an hour or two and try to digest that information to see if I can make it work out on my end.

jforbes, thank you as well for the advice and input, I think you're spot on with your concerns. As I've started developing this rudimentary form the scope quickly expanded as I started to see the potential of Access to tie together so much data. Is there any good reference that you're aware of to give me some basics of database design and normalization? I've been reading up on it at Allen Browns website, but I think another source might enhance my understanding.

Ultimately I think I'm going to press on with making this functionality work, if only as a proof of concept. Then I'll likely pause on form development while I reassess and optimize my database and table relationships.

Thank you all very much, and I'm sure I'll have more questions shortly.

[NeoPa]

Hi ES.

Sorry I've not come back on this earlier. It seems like you've mainly been covered though. Shout if not.

As for your error message on line #6 of your post #4, I'd guess that list_NotCovered is a ListBox control. They don't have a property called Length. Typing in lower case is generally a good indicator of what isn't there. If Length were a valid property of that class then it would have been converted to mixed case automatically. The fact that it's still there as length indicates it's not, and that's what the compiler's complaining about.

That's why I never create variable names with all lower case letters. I'd lose a valuable source of help.

[NeoPa]

Also, if you're starting out in Access, you may benefit from a few tutorials (Microsoft Access Tutorials (Strive4Peace)).

In my experience Access, and database work in general, is one of the areas where a decent basis of understanding up front is most important. Table design and structure has to be one of the first tasks, yet we tend to learn about the pitfalls only after we get past that point. My favourite one on design is by a fellow expert here at Bytes.com - Database Normalisation and Table Structures. getting that under your belt before you start can help you avoid a great deal of the problems most novice users fall over.

The best of luck anyway :-)