Re: determining if this is the first new record in a subform
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Thu, 22 Nov 2007 13:49:30 +0900
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" <ArielZusya@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:41C3F825-461D-42F1-938D-6956C31E6427@xxxxxxxxxxxxxxxx
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" wrote:
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
"ArielZusya" <ArielZusya@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A809FE4E-5BC1-44F5-90B3-FAAE090CA71A@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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
>>
>> "ArielZusya" <ArielZusya@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:0DBEF3D6-4F75-4FA0-8107-AFDBCDA505EB@xxxxxxxxxxxxxxxx
>> > 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!
.
- References:
- Re: determining if this is the first new record in a subform
- From: Allen Browne
- Re: determining if this is the first new record in a subform
- From: ArielZusya
- Re: determining if this is the first new record in a subform
- From: Allen Browne
- Re: determining if this is the first new record in a subform
- From: ArielZusya
- Re: determining if this is the first new record in a subform
- Prev by Date: Lotus Notes Appointment
- Next by Date: MDE file
- Previous by thread: Re: determining if this is the first new record in a subform
- Next by thread: Re: Database opens slowly.
- Index(es):
Relevant Pages
|
