determining if this is the first new record in a subform

[ArielZusya]

I've got a form that uses subforms to display/enter data associated with a
parent record. The "parent" is each day on a project and the "child" is each
tool used for the project on that day. So... if project number 37, building
a chair, requires green hammer and a blue saw on 11/27/07 the form would tell
me that "chair" with ID 37 is associated with green hammer (tool1) and blue
saw (tool2). And then if project number 37, building a char, requires a
yellow level (tool3) and a purple tape measure (tool4) on 11/28/07, there
would be a new record for 11/28 with that information in the submenu. I want
to write code that will determine if this is the first new child record
associated with the parent project or if not what the last child record
entered associated with this project is and then populate the child's tool#
with the next sequential tool# but I'm not sure where to even begin (I
probably don't even have the lingo right). In other words, if on 11/29/07 I
go to enter the tools needed that day for the chair project (#37) I'd like it
to know that the next sequential tool # is tool5 even though the current
record is only linked to the 11/28 record by that 37. This sounds so much
more complicated than it actually is... hopefully someone will be able to
understand what I'm trying to do. Any suggestions? Thanks!

[Allen Browne]

The answer to your question will depend on how you have set your tables up.

It sounds like you need to teach Access the steps required to complete a
project. You could define that with tables like this:

Tool table (one record for each type of tool), with fields:
- ToolID (primary key)

ProjectType (one record for each type of thing you build.) Fields:
- ProjectTypeID (e.g. chair)

ProjectTypeStep table
- ProjectTypeID
- StepNum
- ToolID
(Primary key is ProjectTypeID + StepNum.)

Now that the database knows the steps for each type of project, you can
create the tables for the actual projects you do, like this:

Project table (one record for each project you take on):
- ProjectID
- ProjectType
- ClientID (who this project is for.)
- DueDate (when you're supposed to finish this.)

ProjectStep table (one record for each step of a project):
- ProjectID Which project
- StepNum Which Step of the project.
- ToolID: Tool for this step
- StepDate: Date/Time (when you started this step.)

I suspect you already have something like these 2 tables, with the form and
subform. If so, you can use the BeforeInsert event procedure of the subform
to look up the last step, figure out the next step and tool number, and
assign them to this new record.

This is untested aircode only, and needs debugging, but should give you the
idea:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strSql As String
Dim varResult As Variant
Const strcStub = "SELECT StepNum, ToolID " & vbCrLf & _
"FROM ProjectTypeStep INNER JOIN Project " & _
"ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & _
"WHERE ("
Const strcTail = ") " & vbCrLf & "ORDER BY StepNum;"

With Me.Parent
If IsNull(!ProjectID) Then
Cancel = True
MsgBox "Enter the project in the main form first."
Else
strWhere = "ProjectID = " & !ProjectID
varResult = DMax("StepNum", "ProjectStep", strWhere)
If Not IsNull(varResult) Then
strWhere = strWhere & ") AND (StepNum > " & varResult
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If Not rs.RecordCount = 0 Then
Me.StepNum = rs!StepNum
Me.ToolID = rs!ToolID
End If
rs.Close
Set rs = Nothing
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ArielZusya" <Ariel...@discussions.microsoft.com> wrote in message
news:0DBEF3D6-4F75-4FA0...@microsoft.com...

[ArielZusya]

Hmm... well... before I get too deep in here, I want to make sure I follow.
Also, so we're speaking the same language, instead of having you guess at the
structure of my tables I'll just use your example tables to learn how to do
this and then I'll adapt it later to fit my db (which may be the wrong
structure and needs revamping anyhow *GRIN*). So... I think I followed most
of the table structure you wrote about but I want to be sure... so... here's
what I understood your post to need:

Tables:

tblProject.ProjectID (Primary Key – Text – holds the description of the
project)
tblProject.ProjectType (Text – project name from tblProjectType)
tblProject.ClientID (Text – holds the name of the client)
tblProject.DueDate (Date/Time – holds the date the project is due)

tblProjectStep.ProjectID (Primary Key – Text – description from tblProject)
tblProjectStep.StepNum (Primary Key – Text – StepNum from tblProjectStep)
tblProjectStep.ToolID (Primary Key – Text – ToolID from tblTool)
tblProjectStep.StepDate (Date/Time – holds the date that the step happened)

tblProjectType.ProjectTypeID (Primary Key - Text – ex: “chair”)

tblProjectTypeStep.ProjectTypeID (Primary Key - Text)
tblProjectTypeStep.StepNum (Primary Key - Text)
tblProjectTypeStep.ToolID (Text – ToolID from tblTool)

tblTool.ToolID (Primary Key – Text – holds the name of the tool)

Relationships:

One : Many

tblProjectType.ProjectTypeID : tblProjectTypeStep.ProjectTypeID
tblTool.ToolID : tblProjectTypeStep.ToolID
tblProjectTypeStep.StepNum : tblProjectStep.StepNum
tblTool.ToolID : tblProjectStep.ToolID
tblProject.ProjectID : tblProjectStep.ProjectID

Is that right so far? I didn't want to start teaching myself what the code
means before getting the tables right. Incidentally, this is also my first
experience with using two fields as primary keys (I feel like such a n00b).
Anyhow... let me know. Thanks for your help (and patience *GRIN*)!

--Ariel

[Allen Browne]

Tables look right. I would probably use AutoNumber for the ID fields rather
than text, but it would work as you have it.

There is no direct relationships between:
tblProjectTypeStep.StepNum : tblProjectStep.StepNum

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ArielZusya" <Ariel...@discussions.microsoft.com> wrote in message

news:A809FE4E-5BC1-44F5...@microsoft.com...

[ArielZusya]

Hi Allen,

Thanks for all your help. Maybe it's the recent drop in temp or the
upcoming trip to tryptophan land, but I'm feeling lost and confused. So...
first... I tried changing the ID fields to autonumber and it gave me all
sorts of errors in the subform... so I switched back to text. Second, I got
rid of the relationship you suggested should not have been there and added
one more relationship that was missing from my original list:

One : Many
tblProjectType.ProjectTypeID : tblProject.ProjectType

Third, to avoid confusion I made the following changes:

tblProjectStep.StepNum is now tblProjectStep.ProjectStepNum
tblProjectTypeStep.StepNum is now tblProjectTypeStep.TypeStepNum

Then, I changed the code accordingly:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strSql As String
Dim varResult As Variant

Const strcStub = "SELECT ProjectStepNum, ToolID " & vbCrLf & _

"FROM ProjectTypeStep INNER JOIN Project " & _

"ON ProjectTypeStepID = Project.ProjectTypeID " & vbCrLf & "WHERE ("
Const strcTail = ") " & vbCrLf & "ORDER BY ProjectStepNum;"

With Me.Parent
If IsNull(!ProjectID) Then
Cancel = True
MsgBox "Enter the project in the main form first."
Else
strWhere = "ProjectID = " & !ProjectID

varResult = DMax("ProjectStepNum", "tblProjectStep", strWhere)
If Not IsNull(varResult) Then
strWhere = strWhere & ") AND (ProjectStepNum > " & varResult

End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql

Set rs = DBEngine(0)(0).OpenRecordset(strSql)

If Not rs.RecordCount = 0 Then

Me.ProjectStepNum = rs!ProjectStepNum

Me.ToolID = rs!ToolID
End If
rs.Close
Set rs = Nothing
End If
End With
End Sub

In terms of forms I just used the form wizard to create a justified form
based on tblProject for the main form (frmProject). I then used the wizard
to create a justified form based on tblProjectStep for the subform
(frmProjectStep). I then set the sub as a continuous form and added your
code to the "BeforeInsert" function. Finally, I added the subform to the main
form. When I open the main form and start typing something into the subform,
your code catches the "enter something in the main form first" errors. The
trouble is, when I have something in the main form and then start entering
something in the subform it gets caught at:

Set rs = DBEngine(0)(0).OpenRecordset(strSql)

with an error of:

Run-time error '3296':

Join expression not supported.

What am I doing wrong? do I have all of this setup properly now? Thanks
again for all your help!

Ariel

[Allen Browne]

Okay, so now you are down to debugging the code, and the SQL statement is
incorrect.

It doesn't look right to match the ProjectTypeStepID to the ProjectTypeID. I
imagine ProjectTypeStep would have a ProjectTypeID field? And the FROM
clause should match ON this? Perhaps:

Const strcStub = "SELECT ProjectStepNum, ToolID " & vbCrLf & _
"FROM ProjectTypeStep INNER JOIN Project " & _

"ON ProjectTypeStep.ProjectTypeID = Project.ProjectTypeID " & _
vbCrLf & "WHERE ("

To debug it, add the line:
Debug.Print strSql
Then when it fails, open the Immediate Window (Ctrl+G) to see what the bad
SQL statement looks like. Mock up a query, and change it to SQL View (View
menu in query design) to see what the right SQL statement would look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ArielZusya" <Ariel...@discussions.microsoft.com> wrote in message

news:41C3F825-461D-42F1...@microsoft.com...