Stuck! Long check list in subforms and don't know how to append to a new table (1 Viewer)

[Griztkojr]

Good evening everyone.

I used to know how to do certain little things in Access, but that was a long time ago. I didn't know much about access or developing then and I know less now.

I have a table called tblPunchlist with the fields - ID_Punlist, id_room, id_item, YesNo. In this table I have a list of 152 checklist items. In another table I have tblStaff with the fields ID_Staff, Firstname, Lastname, etc. Then I have two other tables called tblPunchInspect (ID_PunchInspect, Prop_Code, Unit, DatePunch, ID_Staff) and tblPunchispectDetails (ID, ID_PunchInspect, ID_Action, YesNo)

In an Access Form, I need to be able to select the name of the staff member, the property (in another table) and the Apt Number (also in another table) from different combo boxes and then, located in different tabs to keep the checklist organized, I need to be able to check the checklist of 152 items and append all the information into tblPunchInspect and tblPunchInspectDetails accordingly.

I did an initial layout of the form but I have no idea how to get it to work.

Thank you in advance for your time.

 

[Griztkojr]

Did I ask something in a wrong way or manner?

 

[VilaRestal]

Not at all. I just think it's just going to be a complicated one and people have been avoiding tackling it.

If I understand what you want, I think you're first going to want to relate tblPunchispectDetails to tblPunchlist (I'm treating tblPunchlist as a lookup table, not a globally shared dynamic data table, in which case you can get rid of its YesNo field) by adding a foreign key field to tblPunchispectDetails.

Then I think at some point (when Staff, Property and Appt are selected) you'll run code that appends one record to tblPunchInspect for those values and appends all the records from tblPunchlist to tblPunchispectDetails with that new tblPunchInspect record's ID (both those appends if those records don't already exist in those tables). tblPunchInspect will then be the recordsource of your subform with Master/Child fields Staff, Property and Appt. It will have another subform based on tblPunchispectDetails with Master/Child field ID_PunchInspect.

So quite a complex design and some reasonably sophisticated coding. These tickbox ideas always seem so simple in concept but in Access (unless you have a field per tickbox) they're not.

 

[Griztkojr]

Not at all. I just think it's just going to be a complicated one and people have been avoiding tackling it.

If I understand what you want, I think you're first going to want to relate tblPunchispectDetails to tblPunchlist (I'm treating tblPunchlist as a lookup table, not a globally shared dynamic data table, in which case you can get rid of its YesNo field) by adding a foreign key field to tblPunchispectDetails.

Then I think at some point (when Staff, Property and Appt are selected) you'll run code that appends one record to tblPunchInspect for those values and appends all the records from tblPunchlist to tblPunchispectDetails with that new tblPunchInspect record's ID (both those appends if those records don't already exist in those tables). tblPunchInspect will then be the recordsource of your subform with Master/Child fields Staff, Property and Appt. It will have another subform based on tblPunchispectDetails with Master/Child field ID_PunchInspect.

So quite a complex design and some reasonably sophisticated coding. These tickbox ideas always seem so simple in concept but in Access (unless you have a field per tickbox) they're not.

Thank you for having the courage and time to help me.

I attached my project to make easier to communicate. If you load frmPunchInspect you will see the basic layout I did. I added a button where I'm putting this code in the On Click Event

Private Sub btnSave_Click()
Dim strSQL As String

'if the checkbox is selected/true add the record to tblPunchInspect and tblPunchInspectDetail

If Me.Frame12 Then
strSQL = "INSERT INTO tblPunchInspect ( Prop_Code, Unit, Date_Punch, Id_Staff ) SELECT " & Me.Parent.Prop_Code
strSQL = strSQL & " AS Expr1, " & Me.Parent.Unit & " AS Expr2, " & Me.Parent.Date_Punch & " AS Expr3, " & Me.Parent.Unit
strSQL = strSQL & " AS Expr4, " & Me.id_item & " AS Expr5, " & Me.Frame12.Value & " AS Expr6"
End If
CurrentDb.Execute strSQL, dbFailOnError
End Sub

I do not know how to continue with the code since I do not know much about SQL.

Thank you again for answering my post.

 

[VilaRestal]

Sadly I don't have Access 2010. If you can save it as 2007 format and upload that...

 

[Griztkojr]

Sadly I don't have Access 2010. If you can save it as 2007 format and upload that...

It is saved in Access 2007. I saved it again for 2003.

 

[VilaRestal]

OK thanks for that. The previous one must have got corrupted or something.

Anyway, it's as I feared. What you are trying to do is going to be quite a lot more complicated than you think.

The first thing to do is to sort out your tables to fit in with this concept and that's going to take quite a lot of changes.

Mainly, there is just a PunchList table. That should be a lookup list that another table will use to get the punchlists for each property. But it isn't, you've only got the one and all properties are sharing it. If you tick a box for one property you've ticked it for all.

It's exemplified by the lack of relationships. Access databases are relational databases. They need relationships showing how records in different tables relate to one another. Thinking about this will force you to think about how it's actually going to work.

There is a long way for you to go from where you're at to what you want to achieve. I can't explain it all. I haven't the time and anyway it would be quicker for me to just do it all for you and I haven't the time or the inclination to do that.

So, the first step in this quite long journey is take a long look at the tables and their relationships. As they are you will not be able to achieve what you're trying to.

But don't despair either. You've obviously got a lot of the structure there and it won't be wasted. But for the thing your trying to do (checklist items for each property), you don't have the necessary tables to do that.

 

[Griztkojr]

OK thanks for that. The previous one must have got corrupted or something.

Anyway, it's as I feared. What you are trying to do is going to be quite a lot more complicated than you think.

The first thing to do is to sort out your tables to fit in with this concept and that's going to take quite a lot of changes.

Mainly, there is just a PunchList table. That should be a lookup list that another table will use to get the punchlists for each property. But it isn't, you've only got the one and all properties are sharing it. If you tick a box for one property you've ticked it for all.

It's exemplified by the lack of relationships. Access databases are relational databases. They need relationships showing how records in different tables relate to one another. Thinking about this will force you to think about how it's actually going to work.

There is a long way for you to go from where you're at to what you want to achieve. I can't explain it all. I haven't the time and anyway it would be quicker for me to just do it all for you and I haven't the time or the inclination to do that.

So, the first step in this quite long journey is take a long look at the tables and their relationships. As they are you will not be able to achieve what you're trying to.

But don't despair either. You've obviously got a lot of the structure there and it won't be wasted. But for the thing your trying to do (checklist items for each property), you don't have the necessary tables to do that.

VilaRestal: I appreciate your input. I will study further and get to the bottom of this. What other tables would you add? Thank you again for your assistance. If you ever come to NYC please let me know, I owe you lunch!

 

[VilaRestal]

If you ever come to NYC please let me know, I owe you lunch!

Thanks, I'll look forward to that

Two main things:

I'm not sure what the Selected field does in tblPunchlist. I think you should get rid of it (if it is what I think it is) and perhaps rename the table tllPunchlist to show that it's now a lookup table: data won't be routinely added or changed there.

tblPunchlistDetails does actually more than I though it did. (Again not having the relationships makes it harder to see what's going on.) It will be the main table that I thought you were missing. I think it will need a new field though but I'll come back to that.

One more table I'm sure you'll need is tblPropertyRooms. Listing all the rooms for each property. I expect each room will have punchlist items. And that's the other field need in PunchlistDetails - a field to show which of the property's rooms (not just what type of room) the item is for.

The idea is when a property is added, all the rooms get entered. At that point the user clicks a button that will do the following:
1) Creates a new PunchInspect record for that property
2) Copies all appropriate items from tllPunchlist for each of that property's rooms into tblPunchlistDetails (with the ID of the new PunchInspect record)

Then those subforms will have been populated by records that only apply to that property/punchinspect and if there's three bedrooms then there'll be three sets of bedroom punch items - one set for each room.

It's not as bad as my first impressions led me to believe. Doing the relationships and those changes above are important first step and shouldn't take long. Getting the tables right is vital.

The coding won't be simple but not horrendous either. Sorry to be so pessimistic before. You're 80-90% of the way there table-wise and form-wise.

Edit: I know that's a different mechanism to the way you were doing it before but I don't think you could have got that way to work at least without getting over a lot of hurdles. Your method might have been ok as a one-way process but displaying what it had done required it to go the other way too. Keeping the code out of the subforms and just populating the tables in one big go for each property with records ready to be edited in the normal way will be easier to do and more reliable I think.

 

[Griztkojr]

I Put the selected field because I was thinking of changing the yes/no field to a group box with yes =1 no=2 & N/A =3.

I will work on your suggestions and maybe by next year I'll have something to show you . you said it: it looks simple but it really is not.

 

[VilaRestal]

No it isn't but it shouldn't take that long.

By limiting the code to one place where PunchInspects are created and letting everything else just be standard forms bound to tables with minimal to no code it should make it easier both to make and manage.

I'll try to keep an eye out for your posts on this but there are many others here that can help you follow that plan and I'm sure they will.

I wish you luck.