Auto-Populate Fields in 2 Subforms with 1st Subform Data

[TZyk2003]

Hello all...this board is fantastic! What a great tool it is for me...

Okay, i'll explain my problem a little better than in the title.

I have a subform with say 2 fields. The first field is "Pass" which is the primary key and cannot have duplicate values. The second field is "Planned". This field is populated with an integer.

Currently, when I enter 1 in "Pass", another record (not sure if this is the right term, i.e. the entire "details" section is displayed again underneath the current one) is displayed below the current record with 2 automatically populated in the "Pass" field. I'm not sure how this is happening but it is close to what I want.

Now, I would like when 1 is entered into "Pass" TWO more records automatically pop up below the current one with 2 in "Pass" for the second record and 3 in "Pass" for the third record. In addition, I need the "Planned" field auto-populated for the Pass 2 and 3 records with whatever is in the Pass 1 record "Planned" field.

I hope this makes sense...

THANKS!!

-Tim

 

[Rolliee]

Tim,

May I ask why two more records? What are you going to do with these records?


rollie@bwsys.net

 

[TZyk2003]

Yeah, I don't think "record" is the right term.

I meant the entire "Details" part of the form is repeated underneath the current one. I don't know the offical access term for this except "subform". In general, there is actually only ONE subform called "Project" but multiple instances of itself. Does anyone know what this is called??

The "subforms" will keep track of "Planned" (there are actually 6 diff. planned fields - but if I get one working then I can get the other 5 going as well).

So for Pass 1 "Planned" could be 5. But for Pass 2 and 3 "Planned" will usually also be 5 but not necessarily. Hence, I need it auto-populated once the user enters values into the first "subform."

Make sense?

THANKS!!!

-Tim

 

[Rolliee]

I am not sure. You may mean a form and a child form where the child form is tied to the main through a specific field. Perhaps you should send me a zipped copy of the mdb and what you wish it to do. I would look at it and return my answer.

rollie@besys.net

 

[TZyk2003]

Thanks so much for the e-mail Rolliee...your code worked great (after tweaking it a bit) except for one small problem.

When there is no "Current Record" then it gets an error. (saying there is no Current Record). I added a button that once clicked fills in the Pass 2 and Pass 3 stuff perfectly. However, it only works when the Pass 1 record has been saved in the database. (i.e. there IS a current record)

Now, with the subform Project Test the Pass 1 record isn't recorded into the database until the user clicks out of the subform. So I I click the button before clicking outside the subform it gets the "No Current Record" error. And if I click outside the subform and THEN click the button in the subform Pass 2 and Pass 3 are populated perfectly.

How can I deal with this Current Record issue?

I tried some VB code that opens the Project_Test table and checks to see if there is a Pass 1 record (aka Current Record) and if there isn't then add it to the database. But the problem is the record is added but then we I click outside the subform it automatically tries to add it again causing a "Duplicate Primary key" error...

Is there an easy way around this problem? here is the code:

Private Sub Command37_Click()

Dim rs As DAO.Recordset, SQL As String, savPMT_No As String, savPass As Integer, savTest_Cond_Cycle_0_Plan As Integer
Dim PTest As String
Dim db As Database

PTest = "Forms![Project Test]"

Set db = CurrentDb
Set rs = db.OpenRecordset("Project_Test", dbOpenTable)
Dim RetValue As String

rs.Index = "PrimaryKey"
rs.Seek "=", Me.PMT_No, Me.Pass

' CHECK TO SEE IF PASS 1 IS ALREADY IN THE TABLE

If rs.NoMatch Then
RetValue = MsgBox("Continue?", vbOKCancel)
rs.AddNew
rs("PMT_No&quot = Me.PMT_No
rs("Pass&quot = Me.Pass
rs("Test_Cond_Cycle_0_Plan&quot = Me.Test_Cond_Cycle_0_Plan
rs.Update
rs.Close
Set rs = Nothing

End If

' ADD THE PASS 2 and PASS 3 RECORDS

SQL = "SELECT * FROM Project_Test WHERE PMT_No = '" & Me.PMT_No & "' ORDER BY Pass;"
Set rs = db.OpenRecordset(SQL)

rs.MoveLast
savPMT_No = rs("PMT_No&quot
savTest_Cond_Cycle_0_Plan = rs("Test_Cond_Cycle_0_Plan&quot
savPass = rs("Pass&quot
rs.AddNew
rs("PMT_No&quot = savPMT_No
rs("Test_Cond_Cycle_0_Plan&quot = savTest_Cond_Cycle_0_Plan
rs("Pass&quot = savPass + 1
rs.Update
rs.AddNew
rs("PMT_No&quot = savPMT_No
rs("Test_Cond_Cycle_0_Plan&quot = savTest_Cond_Cycle_0_Plan
rs("Pass&quot = savPass + 2
rs.Update
rs.Close
Set rs = Nothing


'Me.Requery
'Me.Refresh

End Sub

 

[Rolliee]

check to see that rs.bof and rs.eof is true which means there is no record and modify the code with an 'if' statement.


rollie@bwsys.net

P.s. Remember to check the "Mark this post useful if you fiind it so..."

 

[TZyk2003]

Okay, but how does that help me?

If rs.BOF is TRUE and rs.EOF is TRUE then there is no current record and the same error occurs saying "There is on Current Record". There will NEVER be a current record until the user clicks out of the subform. So what can I do?

Many thanks!

-Tim

 

[Rolliee]

Tim,

You have to get in front of the code that tried to open that table. You are not adding subforms, you are adding records to a subform. When you open the recordset and find that there are no records - with the EOF and BOF, open another form and enter the PMT_NO. If you already 'know' the PMT_NO from your main form, then do the same addnew's with the PASS numbers 1 and 2 instead of OldPASS + 1.

Does that help???

Rollie E