Help with Populating Fields in a Tabbed Subform

[SoleAris]

Hey everyone,

I have a Form that keeps records of each Machine my company has. It contains a Tab Control with 5 tabs (pages), each containing one subform in them. In one of these pages, I have a Preventive Maintenance subform that contains records with information. One of the fields (Repairs) is a Yes/No data type that I have put an "On Click" event behind, which takes you to a new record in the next page in my tab control.

In this tab, I have a Repairs subform that I wish to populate using information filled out in the Preventative Maintenance subform. I would like the fields "Date" and "Who By" to transfer over automatically just as they are listed in the Preventive Maintenance form. I would like the "Description" field to also carry over to the Repairs subform while picking up the phrase "REPAIRS:" before the typed information.

I have decent experience with SQL statements, although VBA I do not, and this is why I am having trouble figuring this out. Any help would be great!

Here is my current code which takes me to the proper tab and generates a blank new record that I am trying to fill:

Private Sub Repair_Click()
 
Forms![Machines].SetFocus
    Me.Parent!TabCtl0.Pages("Repairs Tab").SetFocus
    DoCmd.GoToRecord , , acNewRec
 
End Sub

 

[pbaldy]

Neither the tab control nor the page are part of the syntax:

http://www.mvps.org/access/forms/frm0031.htm

 

[SoleAris]

I didn't think so either. I have tried different variations such as:

Forms![SF_Repairs].[Date] = Date
Forms![SF_Repairs].[Date] = Me.Date (If I wanted to reference specifically the value in my other subform)

And others, but I seem to keep getting a missing/invalid reference to either the subform or Access considering "SF_Repairs" a field which it obviously will not find.

Thanks for the quick reply, I will check this guide out and try again. Will post results soon.

 

[SoleAris]

Alright, so I am still a little bit stuck with this today. That link is a great guide that you posted, however I am confused as to where my situation fits into that scheme.

When I attempt to write my code, using the "Me." logic prompts controls within the Preventive Maintenance subform, since I am writing an "On Click" event for a control located within that subform. I am attempting to, however, populate values for the Repairs subform that I switch focus to. This is where Access is unable to find any field reference that I give it.

By reading the definitions of that article, I really can't figure out what to consider my Repairs subform and therefore the correct code to write for it..Since each of my subforms is under a tabcontrol of my main form, I consider them all at the "Subform1" level, just on different pages..

Anyone with an idea for me?

 

[pbaldy]

Can you post the db here?

 

[SoleAris]

Can you post the db here?

Done. So for clarity purposes as you look at this:


The main form holds the general information for each machine with subforms in a tab control for that machine underneath the basic information. Here you'll see 5 subforms, ranging from Notes to Breakdowns of a machine.

The ones I am interested in are the "PrevMaint" subform, where you will see the event written to the "Repair" Yes/No field on the subform, and the "Requests" subform (which I was calling Repairs to make it easier to understand).

You'll find that as you select the Repair button from within the "PrevMaint" tab, you will be switched over to the "Requests" tab, which is where I am looking to add in that information automatically (Remember: the Date, By, and Description with a concatenated version).

Thanks for the help~


Note: Please excuse the rather dull appearance of the db..this was one that was made in 2000 and was just handed over to me to update and improve. Functionality before aesthetics~

 

[pbaldy]

Can't test without the tables, but try this after the setfocus and new record lines:

Forms![Machines].T_Requests.Form.DateRequested = Date

 

[SoleAris]

Can't test without the tables, but try this after the setfocus and new record lines:

Forms![Machines].T_Requests.Form.DateRequested = Date

This gives me an "application-defined or object-defined error", which is one I haven't gotten myself yet.

This may not help, but I was able to use the "DoCmd.OpenForm" alternative, which made the subform it's own window. From there, it was extremely simple to fill in the information needed (using "Forms![SF_Requests].[Date] = Date"), however it wasn't as smooth as I'd like, which is why I chose to keep the subforms in a tabbed form.

I'm not sure why keeping it in a tabbed format is giving me errors. The only thing I can think of is that it's still held under the main form this way, and therefore requires a bit different syntax, yet I can't figure it out

 

[pbaldy]

Well, if you can post the back end I can test.

 

[SoleAris]

Converted the tables to local tables. My apologies for not realizing that earlier. Thank again for the help! I'm continuing to attempt to fix it on my own as well~

 

[pbaldy]

Not sure why you'd get an error. Just tested this:

  If Repair Then

    MsgBox ("Please complete the following Request Form for repairs on this Machine")

    Forms![Machines].[MachineID].SetFocus
    Me.Parent!TabCtl0.Pages("Requests Tab").SetFocus
    DoCmd.GoToRecord , , acNewRec
    Forms![Machines].T_Requests.Form.DateRequested = Me.DueDate
    Forms![Machines].T_Requests.Form.issue = Me.Description & " some extra text"
  Else

    MsgBox ("No repairs necessary")
    Forms![Machines].[MachineID].SetFocus
    Me.Parent!TabCtl0.Pages("Requests Tab").SetFocus
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.RunCommand acCmdDeleteRecord

  End If

And got the attached.

 

[SoleAris]

Bizarre. Rather than putting in the actual table name "T_Machines_Requests" which is a naming convention we used to define child tables in relation to their parent table, using "T_Requests" works perfectly..But for the life of me I can't find a single record source that uses T_Requests!?

 

[SoleAris]

Aha! The Name is where it's located as "T_Requests"..had no idea that I needed to change that from the original table source. Such a small, yet frustrating mistake!

Thanks for the help Paul, you were awesome!

 

[pbaldy]

T_Requests is the name of the subform control, which is what you need there. See picture.

 

[SoleAris]

Yeah, edited my post because I found the real problem..I guess I somehow missed changing that name months ago when I transferred everything to a single backend

 

[pbaldy]

Glad we got it sorted out. Welcome to the site by the way!