Update a field in a Table Based on Records Selected from a List Box

[Fsurf02]

Views: 6 Size: 256.7 KB">Access Forum Help Screenprints 11.28.12.zipOk, please bare with me because I am a pretty new at Access and VBA programming/etc and I am trying to build what I thought was a relatively simple database for my company's in-house mechanic's shop for tracking repairs and maintenance on our vehicles/equipment.


I have a Repair and Maintenance Job table that tracks the jobs by JobNumber (autoassigned PK) and a part table for which I can track individual parts by CJBPartNumber (auto assigned PK). I have a one (Jobs) to many (parts) relationship based on JobNumber (foreign key in part table). I foresee that the users will need to add parts to jobs in two different ways: 1) sometimes they order parts specifically to do a job and 2) sometimes they will already have parts in stock that they previously entered into the system and use them on jobs. So I have two buttons on a Job Entry form that allow them to add new parts directly to jobs and also add parts that are already in the system (i.e. stock or inventory parts) to jobs. I have a pretty simple form that takes care of the first task of adding any new part to any particular job. I also have a good idea of how I want the user to be able to add existing parts to jobs but I am not sure how to go about executing it. First, I created an unmatched records query (unmatchedparts) that pulls the records of parts with no associated job number. Then I created a form and added a List Box based on the unmatchedparts query (used as Row Source) which allows multiple selections so that the user can view all parts that are in stock not associated to a job yet.


What I want the user to be able to do is select a part or part(s) from that list box and assign the job number they were working on to it. I was thinking they could click a a command button that says something like "Add Selected Part(s) to current Job" and the On Click event code would go to those parts in the parts table (I am assuming based off the Primary Key CJBPartNumber?) and input the associated JobNumber into the JobNumber field in the Parts Table - thereby assigning those parts to that job. The Job Number would come from the previous form (which is actually a Job Entry Subform that is part of the Vehicle and Equipment form)


As I said, I have a pretty good idea of what I want the code to do and no clue on how to write it. Any help with the VBA coding necessary to do what I am trying to do would be greatly appreciated. I have looked everywhere and typically I can find something close to what I need done, tweak it, and it work out but I have had no such luck with doing this.Attachment 196895


I have also attached a word document that should atleast help you visualize what I am trying to do with my forms. Please let me know if you need additional information.

[ssanfu]

For the multiselect list box see : http://www.baldyweb.com/MultiselectAppend.htm

jobs by JobNumber (autoassigned PK)

If you mean autonumber type field, you shouldn't do that. The autonumber type was not designed to have any meaning. It was designed to provide a unique value for a record. It is not guaranteed to be positive or sequential. It will not re-use missing numbers. It is only guaranteed to be unique.
An autonumber field should not be seen by users.
See: http://access.mvps.org/access/tencommandments.htm #7

If you need a sequential number you will have to "roll your own" with code. Here is one example: http://www.baldyweb.com/CustomAutonumber.htm

[Fsurf02]

Thanks for the response. I will have to fiddle with the code provided for the multi-select listbox from baldyweb. I think I already saw someone in another thread try using his code for a somewhat similar task and I am currently in process of trying to modify their code to do what I need, but it is difficult due to my fundamental lack of understanding of writing/reading/understanding code to begin with (working on that!).

After looking at his code, my situation is slightly different as I don't necessarily need to add a new record to the table based on my listbox, but simply just update or populate one field in the table of each record that was selected in the list box (the JobNumber) since that field would be empty as the part was added before any job was made to add it to it . The field value needs to be the same JobNumber from the previous sub form that I came from and needs to be added to each record. I know it probably seems so simple for some, but like I said, I am fairly new to this code writing stuff, and I really am at a loss as to what needs to be written.

If you mean autonumber type field, you shouldn't do that. The autonumber type was not designed to have any meaning. It was designed to provide a unique value for a record. It is not guaranteed to be positive or sequential. It will not re-use missing numbers. It is only guaranteed to be unique.
An autonumber field should not be seen by users.

If you need a sequential number you will have to "roll your own" with code. Here is one example: http://www.baldyweb.com/CustomAutonumber.htm

As far as the autonumber for the primary key of Job Number and Part Numbers, I really don't intend on the user using those numbers for any reason so you are probably right in the case that I don't really need them to show up anywhere. Rather, as in this case, I more need these fields for the behind the scenes programming that occurs when the user clicks buttons to open other forms. For example, when the user wants to enter a completely new part and it be assigned to the Job they have open on the Job subform, when the user clicks the "Add a New Part" button on the Job Entry Subform, I had to use code in the On click event to open the AddNewPart Form and copy and paste the Job Number field into the Job Number field on the newly opened form and disable that field after it did so (that way it links that part specifically to that job). The user never really knows any of it is happening. Maybe my best solution is to still include the field in the forms but to simply hide it from being visible to the user at all that way it still records the field values.

And again, maybe my novice is showing and there is a much simpler way to do exactly what I am doing, but this is the best thing I have come up with so far. I am open for additional suggestions .

[Fsurf02]

Ok, due to not getting anywhere with the original task I have changed gears slightly.

Just to give you a little background info. My form has changed so now My main form is still the same with information about the equipment (form is called VehicleandEquipmain). This form has a few layers of subforms so please bare with me on this. First I created a tab control on my main form. On the tab named "View/Add Jobs and Parts" I placed a subform for creating and viewing jobs for each piece of equipment. This subform is named "subRepairMaintJobs". Within that subform (inside of "subRepairMaintJobs") I placed another tab control where I will have three other tabs that contain subforms about parts related to each job. I know, you are probably thoroughly confused and it probably isn't the best setup, but was the best way I could come up with designing my forms to do what I think we will need to do.

Well, on one of these tabs relating to parts, I have created a very similar listbox (lstUnusedParts) that still uses the same query I posted in the OP to find the parts whose JobNumber field is Null (JobNumber is the foreign key in the table). This time it is a single select listbox, because now, I think it would be best to have the user double click the items selected in the listbox and then my code to go to the associated records in the Parts table and update the Job Number based on whatever Job I am currently viewing in the subform: subRepairMaintJobs. It would be nice to add a message box that prompts the user to be sure they want to add the part to their job by clicking yes before it runs the code.

Based on some research online, I was able to find some code that seems to do something similar to do this and have given it my best shot but I am still getting a Run-time error '3061': Too few parameters. Expected 1. Here is the code I am trying to debug:

Private Sub lstUnusedParts_DblClick(Cancel As Integer)
CurrentDb.Execute "Update Parts Set[JobNumber]=Forms![VehicleandEqupMain]![Repair & Maintenance Jobs subform].Form![JobNumber] WHERE [JobNumber] = " & Me.lstUnusedParts.Column(0)
End Sub

Can someone please point me in the right direction. If you need any additional information on the structure of the forms/subforms etc I will be glad to give it to you. When I get some more time I will provide updated screen shots of the forms to give you a better idea of what my form structure is.

Thanks again!

[ssanfu]

but I am still getting a Run-time error '3061': Too few parameters. Expected 1.

You need to concatenate the value to the string.
Again, see Baldy Web >> http://baldyweb.com/BuildSQL.htm

Private Sub lstUnusedParts_DblClick(Cancel As Integer)
    Dim sSQL As String

    sSQL = "Update Parts Set[JobNumber]=" & Forms![VehicleandEqupMain]![Repair & Maintenance Jobs subform].Form![JobNumber]
    sSQL = sSQL & " WHERE [JobNumber] = " & Me.lstUnusedParts
    
    'set a breakpoint and see what the string look like
    'open the immediate window
    Debug.Print sSQL

    CurrentDb.Execute sSQL, dbFailOnError

End Sub

And Yes, I am totally confused
More info, screenshots, a demo dB w/just a couple of records would help .

Seems to me that since you are opening a form to add the parts, you could pass the job number using the OpenArgs argument. Then you souldn't have to use anything like
"Forms![VehicleandEqupMain]![Repair & Maintenance Jobs subform].Form![JobNumber]"

Leaving the JobNumber on the form but hidden is a good idea.

----------
Important: You should only use letters and numbers and the underscore as object names in Access (no spaces or special charterers)
See "The Access Web" at http://access.mvps.org/access/tencommandments.htm

The spaces and/or special charterers WILL cause you major headaches......

[Fsurf02]

Thanks so much for the response ssanfu.

With a few tweaks I was able to get the code you provided to do exactly what I was wanting it to do! I also took your advice and updated my forms/etc so that there are no more spaces and symbols, etc. So you know my Main form is now named VehicleandEquipMain (added an i) and the job Access Forum Help Screenprints 12.3.12.zipsubform is named subRepairMaintJobs.

I was actually pleasantly surprised how little tweaking it took to your code considering the lack of information I provided and what I did provide was so confusing. For that I apologize. I have included a screen print that shows the structure of the form and it's subforms below. Hopefully, this will help you visualize what I am trying to accomplish without attaching a demo database (unless of course you think it would be helpful for other users, then I don't mind including it).

With that said, I still want this code to do the following two items before executing:

1. I would like it to check to see if the JobNumber control in the subRepairaMaintJobs subform is blank, and if it is blank, to return a message box that says something to the effect of "You must create a job before adding any parts to it". Otherwise, run the code.
2. Additionally, I would like for a message box to appear asking the user if they are sure they want to add the part to the Job, with yes/no option. User selects yes, it executes the code, if the user selects no, it cancels the entire procedure.

I have made an attempt at wrapping the code you provided with my best attempt to do the two procedures above. This is what I got:

If IsNull(Forms![VehicleandEquipMain]![subRepairMaintJobs].Forms![JobNumber]) Then
MsgBox "You must create a job before adding any parts to it", vbOKOnly
Else
Dim Msg As String, Ans As Variant
     Msg = "Are you sure you want to add this part to your Job?"
     Ans = MsgBox(Msg, vbYesNo)
     Select Case Ans
         
    Case vbYes
         
       Dim sSQL As String


    sSQL = "Update Parts Set[JobNumber]=" & Forms![VehicleandEquipMain]![subRepairMaintJobs].Form![JobNumber]
    sSQL = sSQL & " WHERE [CJBPartNumber] = " & Me.lstUnusedParts.Column(0)
    
    'set a breakpoint and see what the string look like
    'open the immediate window
    Debug.Print sSQL


    CurrentDb.Execute sSQL, dbFailOnError


         
    Case vbNo
    GoTo Quit:
    End Select
     
Quit:
End If
End Sub

Now, when I try running the code I get a Run-time error '438': Object doesn't support this property or method and when I go to the debugger it highlights the first line of code (that I have in red font above). For some reason I can't seem to get the hang of naming objects within subforms and this is apparently the hangup.

If you could help me tweak this final bit of code, I think that will get me exactly what I want done. Thanks again for all your help!

[ssanfu]

So you know my Main form is now named VehicleandEquipMain (added an i) and the job subform is named subRepairMaintJobs.

Did you also rename the sub-form control (not the subform)???

I modified your code. Is this what you want?

Private Sub lstUnusedParts_DblClick(Cancel As Integer)
   Dim sSQL As String
   Dim Msg As String, Ans As Variant
   Dim vJobNum As Variant

   'get job number
   ' because the job number is on the subform "subRepairMaintJobs", you should
   'be able to refer to it using "Me.JobNumber"

   '   vJobNum = Nz(Forms![VehicleandEquipMain]![subRepairMaintJobs].Forms![JobNumber], 0)
   vJobNum = Nz(Me.JobNumber, 0)
   'for debugging, show the value of vJobNum
   '   MsgBox "Job # = " & vJobNum
   
   If vJobNum = 0 Then 'no job # selected
      MsgBox "You must create a job before adding any parts to it", vbOKOnly
   Else
      Msg = "Are you sure you want to add this part: " & Me.lstUnusedParts.Column(1) & " to your Job?"
      '      Msg = "Are you sure you want to add this part to your Job?"
      Ans = MsgBox(Msg, vbYesNo)

      Select Case Ans
         Case vbYes
            sSQL = "Update Parts Set[JobNumber]=" & vJobNum
            'the bound column is the default, so you don't have to type ".Column(0)
            sSQL = sSQL & " WHERE [CJBPartNumber] = " & Me.lstUnusedParts

            'set a breakpoint and see what the string look like
            'open the immediate window
            Debug.Print sSQL

            CurrentDb.Execute sSQL, dbFailOnError

         Case vbNo
            'do nothing
      End Select
   End If

End Sub

my situation is slightly different as I don't necessarily need to add a new record to the table based on my listbox, but simply just update or populate one field in the table of each record that was selected in the list box (the JobNumber) since that field would be empty as the part was added before any job was made to add it to it .

What???
I really don't understand what you are doing.

I would expect a "customer" table, a "jobs" table with a customer_FK field, a "JobsParts" junction table with Jobs_FK and Parts_FK fields.


Table: Customers
------------
CustID (PK)
'other fields

Table: Jobs
------------
JobID (PK)
CustID_FK
'other fields

Table: AllParts
-----------
AllPartsID (PK)
'other fields

Table: JobsParts
----------------
JobsPartsID (PK)
JobID_FK
AllPartsID_FK
'other fields


It doesn't really matter if I understand or not, as long as you are getting the results you want.

--------------

You might want to change the text in your label to remove the "(s)" after Parts in the label that begins with "Select the Part(s)..."

BTW, you could use a multi-select list box, it just takes a little more code to add the parts since you have to have a loop to get the selected items.

[Fsurf02]

Did you also rename the sub-form control (not the subform)???

Yes, I did do that, but thank you for making sure that I did.

I modified your code. Is this what you want?

After reviewing your code, I plugged it in and ran it and still have the same issue with the JobNumber field. Not sure what I have done to create this issue, but I am still getting a "Compile error: Method or data member not found" and it highlights the .JobNumber in the code.

What???
I really don't understand what you are doing.

I would expect a "customer" table, a "jobs" table with a customer_FK field, a "JobsParts" junction table with Jobs_FK and Parts_FK fields.


Table: Customers
------------
CustID (PK)
'other fields

Table: Jobs
------------
JobID (PK)
CustID_FK
'other fields

Table: AllParts
-----------
AllPartsID (PK)
'other fields

Table: JobsParts
----------------
JobsPartsID (PK)
JobID_FK
AllPartsID_FK
'other fields

Currently, my table structure is as follows:

Table: Vehicles_Equip
------------
EquipmentName (PK)
'other fields

Table: Jobs
------------
JobNumber (PK)
EquipmentName_FK
'other fields

Table: Parts
-----------
CJBPartNumber (PK)
VendorID (FK)
JobNumber (FK)
PartNumber (this is the Vendor's Part Number)
'other fields

Table: Vendors
----------------
VendorID (PK)
'other fields


So as you can see I don't have two parts tables or a customer table. Keep in mind this is not for any customer's but rather an internal tracking database to track our repairs and maintenance on our fleet of vehicles and equipment.

It probably wouldn't be a bad idea to have two parts tables as you have shown, but I figured it may be more efficient to have one since the only difference in the two tables essentially would be that one would have no job number field (fk) and the other would. It may make it easier to write code to transfer these parts, however. Nevertheless, currently I have a form that allows the user to add "Stock/Inventory" Parts and I have simply excluded any JobNumber control from the form so that way I know the parts will not be assigned to any job. My listbox (lstUnusedParts) that I am using to select the parts is using a query that finds parts whose JobNumber field is null. That was all I was trying to explain with the blurb:

my situation is slightly different as I don't necessarily need to add a new record to the table based on my listbox, but simply just update or populate one field in the table of each record that was selected in the list box (the JobNumber) since that field would be empty as the part was added before any job was made to add it to it .

BTW, you could use a multi-select list box, it just takes a little more code to add the parts since you have to have a loop to get the selected items.

Yeah, that is what I initially wanted to do, but was stumped on how to write the code which was what the OP was about. That is actually why my text box above the listbox is still outdated and needs to be updated as you noticed (with the Part(s)/Part wording). I don't forsee too many instances that the user will need to select a lot of multiple parts so I am thinking the double click method will probably be sufficient however, if you are really feeling up to it, I am open for any code suggestions to get it done with that method as well !

[Fsurf02]

For your reference, I have attached my full database scrubbed of any sensitive data. The database is somewhat a mess right now (as far as a bunch of forms that I won't even be using).

The best thing you can do is start at the MasterDirectory form and click on the button "706" on the first tab. This has been my dummy record where I have been recording/testing a lot of different job/part records. The third tab labeled "Add Existing Parts to Job" is where I am working on the listbox. Again, let me know if you have any questions. Thanks!

Shop Database v3 Online Help Version.zip

[ssanfu]

I'll look at it tonight.

[ssanfu]

OK, here we go.

I have just started to use A2010 (hate it), so it took me a while to figure out what you have done.
I modified the MasterDirectory form... The problem is that if you have to add a new piece of equipment, you have to redesign the forms, queries and reports.

You had a couple of tables with a text field as the PK. From what I have read, you can do this but it is better to use a Autonumber if there are not any good natural candidates. Except for small lookup tables, I always have an autonumber as the PK.
So I changed the structure of a couple of tables.

Then, at long last, I solved the dblclick code problem. You were on a sub-sub form, so I used the "Parent" property to get the jobnumber.

Also in the attached zip is a jpg of how I would normalize the table structure. Its not complete... there are still a couple more field that I would change/add/move. For example, I would add a "Active" field (boolean) to be able to take a piece of equipment out of service without deleting the history.

[Fsurf02]

THANK YOU! THANK YOU! THANK YOU!! Your code works perfectly and you sir, are the best! Man, If I only had someone like you around to really pick your brain on a lot of this stuff as it came off I could really be going places! But anyhow, I do like the changes you made and I think I will implement most, if not all of the changes to the table structures you recommend as well. I also loved the main directory you created (should have thought of that before!) and I think that is a great idea on the active field for the equipment.

I actually somewhat impressed myself by solving a couple other tasks I wanted done related to this same issue I was having. These were two additional items that related to this same form that I wanted to see done:

1st task: After the user double clicks a part on the lstUnusedParts listbox, and they select "yes" to add that part, I want the listbox to requery and remove that part from the listing.
This was pretty simple and I added the following line of code after the End If of the code you provided.

Me.lstUnusedParts.Requery

2nd task: Then, if the user were to navigate back to the first tab (the tab labeled "Parts Listing for Job") I want the part that they just added to show up in the list of parts in the subform within that tab. Well, after a little bit of research, I was actually able to use the immediate window to help me figure out the correct syntax for updating the parts listing subform on that tab. The code was is as follows:

Private Sub TabCtl33_Change()
Forms!VehicleandEquipMain!subRepairMaintJobs!PartListingSubformPerJob.Requery
End Sub

This code was added to the On Change event for the entire tab control (i.e. TabCtl33 -not the individual tab itself).

I know, pretty simple stuff probably in your mind, but baby steps for me! And, I'm sure I have probably done it the "ugly" way, but it seems to work and no error's thus far, so that is a plus.

Anyhow, thanks again so much for your guidance and patience with me. I am truly indebted to you!

[ssanfu]

Excellent! Good job.

Remember to do the testing on the new structure in a TEST dB, NOT your current production dB!!

In my suggested structure, the "Parts" table would hold all parts for all vehicles/equipment (with a FK back to the "VehicleEquip" table. For Example, there would only be one record for "spark plug" for "Crew Truck" (EquipmentClass). So there would need to be a field for quantity in the "JobParts" table.

Would you like me to flesh out the tables/structure a little more? It might take a few days...... Maybe it will give you some more ideas....

[Fsurf02]

Excellent! Good job.

Remember to do the testing on the new structure in a TEST dB, NOT your current production dB!!

Yeah, currently I am still working in essentially the unscrubbed version of the database i posted on here. I have just copied any code and forms you have suggested into my database. Plus, nothing has been put 'online' yet, I am still in the early stages of designing the database and all the jobs and parts entered thus far are simply dummy records that I have added to test the forms, etc. Once I get the basic structure of the tables and forms I think I want, I will delete all the data in the tables other than vehicles and equipment, split the database and define all of the users/user groups/etc to protect and secure it.

In my suggested structure, the "Parts" table would hold all parts for all vehicles/equipment (with a FK back to the "VehicleEquip" table. For Example, there would only be one record for "spark plug" for "Crew Truck" (EquipmentClass). So there would need to be a field for quantity in the "JobParts" table.

Hm...I think I will need to get further understanding of exactly how this would work. I'm not too positive I follow, but it sounds as though you are saying the parts table would house all possible part types to be used in jobs and the "JobParts" table would track the instances of these types of parts being used?

If that is what you are saying, I am not so sure that will work as we would have a difficult time getting started as it would be tough to create a comprehensive list of all the different part types to start (as there will literally be thousands of possible parts). Additionally, how do you handle instances where we purchase the same part type from multiple vendors who each have their own part number they assign to them. For example, we may purchase "Spark Plugs" from Vendor A (part number 1125) and Vendor B (part number 1178)?

And again, I may be just totally confused on how this would all work. I took accounting information systems classes back in college and vaguely remember going over database structure to handle Parts (both types and individual part instances), but I think I may just be unclear on how exactly the information would flow and link together.

Would you like me to flesh out the tables/structure a little more? It might take a few days...... Maybe it will give you some more ideas....

Absolutely! If you have the time and energy I am open to any suggestions you may have! Thanks again for helping this newbie out!

[ssanfu]

but it sounds as though you are saying the parts table would house all possible part types to be used in jobs and the "JobParts" table would track the instances of these types of parts being used?

Exactly!

I am not so sure that will work as we would have a difficult time getting started as it would be tough to create a comprehensive list

The parts in the parts table would build up over time. don't have to enter them all in at one time. Kind of like you are doing now.

Additionally, how do you handle instances where we purchase the same part type from multiple vendors who each have their own part number they assign to them. For example, we may purchase "Spark Plugs" from Vendor A (part number 1125) and Vendor B (part number 1178)?

One record for Part Num 1125, "Spark Plug", Vendor A
Another record for Part num 1178, "Spark Plug", Vendor B
You could search for part number or Desc (Spark Plug) or Vendor Name or a combination of the fields.


OK, I'll work on it over the weekend..