How to use a subform to add "progress reports" to main form record

[loslobo]

Hello...

I'm sure that someone has a quick solution to this.

Basically, I've created a form to track maintenance requests for specific types of equipment. Basically it's like a ticketing system.

Once a user creates the request, I want to use a subform (I guess this is the best way) to let them add data to that specific main form record to track the progress of the request until the repair is made and the "ticket" is closed out.

Do I need to create another table for the "progress reports" (for lack of a better term) or should this be done using some sort of an append query?

I've seen plenty of sample databases where they have a box within a form that looks like a datasheet where these types of "update" notes can be added and then submitted with the click of a button, but I've never been able to get at the code behind it.

I'm really frazzled with this and your help would be very much appreciated. Thanks!

 

[CJ_London]

Do I need to create another table for the "progress reports"

yes

but I've never been able to get at the code behind it.

There usually isn't any code. Your new other table will contain a field which is a child relationship to your 'maintenance' table ID. In the subform control properties set the linkchild and linkmaster properties to the names of these fields. In fact if you set the relationship in the relationship window, Access will do this for you automatically

 

[loslobo]

Thanks so much for the advice. I'm going to try this shortly and report back to you.

I'll also let you know if it leads to any other questions!

 

[loslobo]

yes

There usually isn't any code. Your new other table will contain a field which is a child relationship to your 'maintenance' table ID. In the subform control properties set the linkchild and linkmaster properties to the names of these fields. In fact if you set the relationship in the relationship window, Access will do this for you automatically

OK. I have the ID field from my main form as the "child" relationship in the subform.

Now I need to use a bunch of textboxes to populate "progress report" data

For example, on a specific date, a specific action was taken. I need to populate the date field, the action taken field, the current status of the repair ticket, and the name of the person who entered the progress report.

How do the "new" textboxes I'm creating in the subform know how to populate the table that the subform is supposed to be reporting to?

 

[CJ_London]

Sorry, don't understand - the subform control has a sourceobject of another form, the fields you are referring to would be on that form


tblMain - for main form
mainID autonumber - this is the linkmaster
mainDescription text
....
...


tblSubform
SFormID autonumber
MainID long - this is the linkchild
SFormDate date
SFormDesc Text
...
...

 

[loslobo]

Thanks CJ. I'm getting back to this first thing Monday morning and will report progress.

I appreciate you hanging in there with me!

 

[loslobo]

Sorry, don't understand - the subform control has a sourceobject of another form, the fields you are referring to would be on that form


tblMain - for main form
mainID autonumber - this is the linkmaster
mainDescription text
....
...


tblSubform
SFormID autonumber
MainID long - this is the linkchild
SFormDate date
SFormDesc Text
...
...

OK...so all of my text boxes in the SUBFORM are currently UNBOUND. What should they be bound to? The fields in the SUBFORM TABLE, correct?

And when you say "MainID long" do you mean "Long Integer?"

Thanks CJ!

 

[CJ_London]

The fields in the SUBFORM TABLE, correct?

correct, but learn to get your terminology correct - it is the subform recordsouce

And when you say "MainID long" do you mean "Long Integer?"

yes

 

[loslobo]

correct, but learn to get your terminology correct - it is the subform recordsouce

yes

Thanks CJ. Right now, there are no records in the subform table. I was counting on the maintenance update data that I entered to be populated in there.

Then I was going to create a report that pulled data from the tblmain and tblSubform to show the progression of the work order from inception to closeout.

I've been entering data in the subform but honestly, I don't know where its going after it has been saved.

Would it be easier for me to zip the DB and attach it for your review? I think I'm starting to confuse the issues, and maybe by seeing it you'll get a better understanding of what I'm trying to do.

Thanks

 

[loslobo]

OK...I may be on the right track.

I scrubbed my subform and created a new one with the wizard, and selected the correct subform recordsources, and used the main form ID as the "child"

However, now I'm getting a "You can't assign a value to this object" error when I try to enter the date on the subform. Oddly, once I clear the error message it proceeds with no problem.

Working through that issue now....by searching these forums!

 

[loslobo]

OK...so things are going along much better with the subform. Data is populating into the subform table.

I haven't solved the "Can't assign a value to this object" problem yet but I'm working on it.

Next question is how to combine the data from both the main form table and subform table into one report.

Should I merge the main form table data and subform table data into a new table for the purposes of populating the report?

Or is there another way?

Thanks!!!

 

[CJ_London]

Should I merge the main form table data and subform table data into a new table for the purposes of populating the report?

It really depends on what you want to report, it may be you need a query which combines the two tables or it may be you have a main report and a subreport, similar to your form

 

[loslobo]

It really depends on what you want to report, it may be you need a query which combines the two tables or it may be you have a main report and a subreport, similar to your form

My main goal is to have a report that shows the original maintenance request from the main form as the top half of the report, and then a box of data on the lower half of the form that shows all related maintenance progress from the subform.

Thoughts?

 

[CJ_London]

My main goal is to have a report that shows the original maintenance request from the main form as the top half of the report, and then a box of data on the lower half of the form that shows all related maintenance progress from the subform.

In that case, go for a main report with subreport for the maintenance progress

 

[loslobo]

In that case, go for a main report with subreport for the maintenance progress

Yup. I'm working on it right now!

Thanks!

 

[loslobo]

In that case, go for a main report with subreport for the maintenance progress

OK...hitting a wall with this.

I have the main report, and I'm using the Subform/Subreport wizard button to draw the box.

However, I can't get the subform recordsource data to populate in the subreport.

The main form and subform are linked by the main form ID.

What am I missing?

 

[CJ_London]

I'll just put my x-ray specs on....

nope, still can't see what you are missing

Be more descriptive, I've no idea of the relevance of the form info in relation to a report (except perhaps you need to set the linkchild/master fields in the report, same as a form)

I can't get the subform recordsource data to populate in the subreport.

so what is your recordsource? what controls are on the subreport, are they bound etc

 

[loslobo]

I'll just put my x-ray specs on....

nope, still can't see what you are missing

Be more descriptive, I've no idea of the relevance of the form info in relation to a report (except perhaps you need to set the linkchild/master fields in the report, same as a form)

so what is your recordsource? what controls are on the subreport, are they bound etc

I know I'm not making myself clear...I'm sorry. I have a clear picture in my head of how I want this to work but I'm just not able to express it in the correct terms.:banghead:

The main form is the original service ticket for a broken device. The subform is where people will enter ongoing updates about the progress of the repair. They are linked by one "recordsource" (hope I'm using that properly) and that is the main form's ID which is the primary key and autonumbers.

I've had no problem creating the main report using all of the fields from the main form's table (which is called "Ticketing")

The subform is called "Ticket Tracking Subform." The related table for it ("Ticket Tracking") contains the data that was entered in the subform.

However, when I try to "draw" the subreport box into the main report so that I can also display all of the repair progress for a specific device shown in the main report, I can't get the data to populate.

I think I may still have to go with a query, but from your previous advice it didn't sound like it was necessary.

Again, thanks for bearing with me on this.

 

[CJ_London]

Can you confirm that you got your form and subform to work?

If you did, the report and subform is done exactly the same way

 

[CJ_London]

perhaps these links will help you

http://www.databasedev.co.uk/access2007_subreports.html

https://www.youtube.com/watch?v=5LuCohVnVZ4

http://sourcedaddy.com/ms-access/adding-and-formatting-subreports.html