Using Checkbox List to populate an order form

[jcarpenter22]

Hello all,
Been a few years since i worked with access and have ran into a problem that im hoping i can get some help on.

http://www.4shared.com/file/DiUo9D-z/Test_Database.html

See attached hyperlink for the database im working with.
Pertinent Tables
PartsList
Figure
ECOD
tblSelectedPartsIDFK
The intent is to create an ECOD with many parts.
I want to be able to see a figure with the associated parts and then to select the parts with a checkbox.
I created this form with a drop down box without an issue but with 80 pictures and over 2000 parts the drop down box was very ineffective, hence the reason for a checkbox. I found some vb code on this site by a MajP and actually found it quite effective, however, testing it I created FormDemo and it works well but i can not link the ECOD (see form ECOD).
I'ld appreciate it if someone much smarter than myself could take a look at it and tell me where im going wrong.

 

[MajP]

Dim suppID As Long
dim ECODID as long
ecodid = me.ECODID
suppID = item.ListSubItems(3).Text
If item.Checked Then
Call addToList(suppID, ECODID)
Else
Call removeFromList(suppID,ECODID)
End If
Me.Querypartslist.Requery



Public Sub removeFromList(selectedID As Long, ECODID as long)
Dim strSql As String
strSql = "Delete * from tblSelectedPartsIDFK where SelectedPartsIDfk = " & selectedID & " AND ECODID = " & ECODID
CurrentDb.Execute strSql
End Sub

Public Sub addToList(selectedID As Long,ECODID as long)
Dim strSql As String
strSql = "Insert Into tblSelectedPartsIDFK (SelectedPartsIDfk, ECODID) values (" & selectedID & ", " & ECODID &")"
CurrentDb.Execute strSql
End Sub

And you have to admit this is a pretty cool control concept.

 

[jcarpenter22]

Thanks for the code and yes it is a cool concept.
Unfortunatly i still have issues with it. I made the changess in my ECOD form (module) but i still get errors on

Dim suppID As Long
dim ECODID as long
ecodid = me.ECODID
suppID = item.ListSubItems(3).Text
If item.Checked Then
Call addToList(suppID, ECODID)
Else
Call removeFromList(suppID,ECODID)
End If
Me.Querypartslist.Requery

I either get the errors on me.ECODID or me.QueryPartsList
Im starting to think i need to leave this stuff to the professionals Am i just entering it incorrectly? Thanks again.

 

[MajP]

I will take another look. I did not really test it I just looked at the code. Can you says what the errors are.

If you get an error here
ecodid = me.ECODID
I can only be two things.
1)You do not have a control named ECODID on the form in question. In this case the code is probably on the subform and the ECODID is on the main form. To reference a control on the main form from the subform
ECODID = Me.Parent.ECODID
The parent of a subform is the Mainform
2)The other things that is happening is that I set this up assuming that ECODID always returns a number. It may be alphabetic or return a null.
To ensure that it does not throw an error when null
ECODID = NZ(Me.parent.ecodid,0)
If it is supposed to be alphabetic then you will have to do some additional changes.

2)Me.Querypartslist.Requery
Says there is some control on the current form named QueryPartslist. By Looking at the name that is probably not true. Maybe you are trying to requery a subform. I am thinking that subform is also on the main form. If that is the case to requery another subform from a subform you do it like
Me.Parent.OthersubformCOntrolName.Form.Requery

 

[jcarpenter22]

MajP - i actually got it to working but it seems to only complete the one ecod as when i move onto the second ecod all the checks from the first ecod are still checked and the only checks i can enter in the second ecod are boxes that had not been selected.
ECODID is an autonumber on its respective table.
Thanks

 

[MajP]

I will take a look. But my guess is that the only event you are currently using is the on load event. So the form loads, and fills the check boxes. But now you have to call the same code from the forms Oncurrent event. So when you change a record it refills the checkboxes for the new ECOD.

 

[jcarpenter22]

Yes i had ensured it was listed under the oncurrent and not the onload. But no matter what i have attempted, the checkboxes are always marked for the first ecod. I did change the last statement to reflect false, which does clear the checkboxes for the next ecod (and still saves the parts in the tblselectedpartsIDFK), however, it does not allow me to select those parts from the previous ecod.

Public Function IsSelected(suppID As Long) As Boolean
If Not Nz(DLookup("selectedPartsIDFK", "tblSelectedPartsIDFK", "selectedPartsIDFK = " & suppID), 0) = 0 Then
IsSelected = False
End If
End Function

 

[MajP]

I will take a look at it today. If you have an update please post.

 

[MajP]

http://www.4shared.com/file/z-XBsxaV/MajP_LstVw_Parts_V10.html

Here you go.
I did a lot of changes so look at the code and see if you have questions.

I did away with the sub form and put the list view directly on the main form. This solves a lot of issues.

Do yourself a big favor and use a good naming convention. It makes it extremely difficult to debug and understand what you are doing. Never put any spaces in any object names including fields
Use a convention like this.
tblTableName
frmFormName
subFrmSubFormName
qryQueryName
rptReportName
txtBxTextBoxName
mdlModuleName
cmboComboBoxName

That way in code when I see tblPartList (instead of [Part List}) I know it is a table, not a form or variable or anything else.
Or
subFrmPartList is a subform. It is real confusing when you have a subform named QueryPartlist.