Full Version: Structure,Code,Or Both?
UtterAccess Forums > Microsoft® Access > Access Forms
jmcwk
Basically I am trying to determine why I have no problems when using a Table as a record source for a Cascading Combo boxes and a problem comes up when using a query. PartTypeID is on the one side and tblParts is on the many side of a one to many relationship
blPartType
PartTypeID (PK)
Type
tblParts
PartID (PK)
PartTypeID (FK)
Part
Combo Box Updates no problem with the table however when using the query:<
CODE
Private Sub Part_Enter()
    Dim strSQL As String
    
    strSQL = "SELECT PartID, Part FROM tblParts " & _
             "WHERE PartTypeID = " & [Forms]![frmLog]![frmLogDetail].[Form]![PartTypeID] & " ORDER BY Part"
    [Forms]![frmLog]![frmLogDetail].[Form]![Part].RowSource = strSQL
End Sub
Private Sub Part_LostFocus()
    Dim strSQL As String
    
    strSQL = "SELECT PartID, Part FROM tblparts ORDER BY Part"
    [Forms]![frmLog]![frmLogDetail].[Form]![Part].RowSource = strSQL
End Sub

Have also attached an image of the relationship/s
Thank You
Dial222
Hi John
I'm just off for dinner, so don't really have lots of time to look at this but something that did pique my interest is the PartTypeID in both the parts and logdetail tables. What's the thinking behind that?
Carl
Tomolena
I was wondering about the same thing. Three tables actually if you include the PartType table. Anyway have you tried debug.print on the resulting strSQL? Does the resultant datasheet look anything like the table...or are you getting the same message when you try to run it as a query?
jmcwk
The query is updateable and the form updates using the query as the source until I get to the Part Combobox it is at that point that the Error Message occurs. I think it has to do with the Join in tblLogDetail but I am not real sure about that Not real sure I would need both PartID, and PartTypeID in the tbllog detail however that in itself should not cause any problems that I know of??? The Message is appearing from using the query as the recordsource No problems at all when using tbllogdetail by itself.
Jack Cowley
John -
Are your combo boxes in a continuous form? Also, if tblPartsID is in tblLogDetail I don't know that you need tblPartTypesID as tblPartsType is a 'lookup' for tblParts.
If your combos are in a continuous form then take a look at the code you did for Rachel as it worked for her and it should work for you... Be sure your syntax is correct in your SQL statement.
hth,
Jack
jmcwk
Hello Jack! Good to hear from you as always. The combo boxes are on a single form and like I said they work fine IF I use the table rather than the Query matter of fact I used the exact SQL as WE/You used for Rachel. are you saying I do not need tblPartTypesID at all?
Tomolena
Since you have a PartTypeID as a foreign key in two tables, is it possible that the "Forms!..." part of your code is pulling the wrong one?
jmcwk
Tommy,Jack! Heading to lunch while I can but will be back shortly Tommy I think that is a good possibility
Jack Cowley
John -
If you have a PartsType combo and you want the second combo (Parts) to show only Parts with the selected PartType then a query (not the SQL) as the Row Souce should do the trick. Create a query based on tblParts and in the criteria line of the PartTypeID put code like this:
[Forms]![NameOfYourForm]![NameOfPartTypeComboBox]
Save the query and then select it from the list in the Row Source of the Tables combo.
In the After Update event of the PartType combo:
Me.NameOfPartsCombo.Requery
Now selecting a PartType in the first combo should show all the Parts with that PartType in the second combo...
Jack
merlicky
Are the combos in a subform?
The PartTypeID in the tblLogDetail looks like it could be redundant, but it shouldn't have an effect on how a query that doesn’t use that table works. Also, the lost focus event really does nothing since it is updated again on re-entering the Part combo.
That does seem a little confusing to me is that your query is using only one table:
FROM tblParts
so why would there be any problem with a relationship?
merlicky
Another question, or two...
Is PartTypeID the name of a combobox control? Does your Form/SubForm have a controlsource?
If so try using:
PartTypeID.Value
instead of:
[Forms]![frmLog]![frmLogDetail].[Form]![PartTypeID]
since the PartTypeID may be being misinterpreted as the field value, which might explain the relationship issue with just one table in your SQL.
jmcwk
Merlicky,
Are the combos in a subform?
Yes
The PartTypeID in the tblLogDetail looks like it could be redundant,
Have taken it out of tblDetailLog
but it shouldn't have an effect on how a query that doesn’t use that table works.
That table IS part of the table
What does seem a little confusing to me is that your query is using only one table:
I have ALL tbales in the Query for the purpose of showing for example PartID 82 from tblparts is a part=jack hammer Table of course just holding the Foreign Keys PartID,PressID,ToolID, etc.
MainForm tblPress
Subform linked to tblPress by PressID
However this has no bearing on the combos.
merlicky
Based on this bit of code, the only table used to populate the combobox is the "tblParts" table:
Private Sub Part_Enter()
Dim strSQL As String
strSQL = "SELECT PartID, Part FROM tblParts " & _
"WHERE PartTypeID = " & [Forms]![frmLog]![frmLogDetail].[Form]![PartTypeID] & " ORDER BY Part"
[Forms]![frmLog]![frmLogDetail].[Form]![Part].RowSource = strSQL
End Sub
Does this error occur when you move into the combobox, or when you select an item from the combo's list...
With that, does your combobox have a controlsource? or any code in the update events?
jmcwk
I follow what you are saying now regarding the table yes the SQL is using the one table tbl parts The error only occurred when I changed the recordsource of the form itself from the tbllogDetail to the query.
Have Attached maybe be clearer open frmLog mainform frmlogdetail is the sub and you will see frmlogdetail currently has tbllogdetail as the rowsource Combo Part Type and Part work. Goback into frmlogdetail and change the recordsource from the tbllogdetail to qrylogdetail and the error will pop up after entering a value into the Part Combo.
jmcwk
Jack,
did not forget about you have not got that far yet. frown.gif
Jack Cowley
John -
Not to worry as I am keeping busy...
Jack
Jack Cowley
John -
Try the SQL as the Row Source for you Parts combo:
CODE
SELECT tblparts.PartID, tblparts.Part, tblparts.PartTypeID
FROM tblparts
WHERE (((tblparts.PartTypeID)=[Forms]![frmLog]![frmLogDetail].[Form].[PartType]))
Order/>Try the SQL as the Row Source for you Parts combo:
CODE
SELECT tblparts.PartID, tblparts.Part, tblparts.PartTypeID
FROM tblparts
WHERE (((tblparts.PartTypeID)=[Forms]![frmLog]![frmLogDetail].[Form].[PartType]))
ORDER BY tblparts.Part;

Jack
jmcwk
Nope,
olumn Count 3
Column Widths 0;1.5;0
Bound Column 1
Does not requery and when a part is selected status bar reads "To Make Changes To This Field First Save The record"
This is using qryLogDetail as the recordsource for the form itself
Jack Cowley
John -
You have lost me. The only thing that I fiddled with were the two combo boxes in the subform, PartType and Part. Take a look at the attached and see if selection a PartType from the PartType combo box shows you the correct parts in the Part combo box....
Jack
Tomolena
I noticed that you have a Text34 control that displays an accumulated time value. When you get this thing working, here is a neat DateTime function to use in that field. It's optimized for speed so that you can use it in the form's OnCurrent event with little, if any, performance impact, or you can use it in your expression (=f_fTime2Str([dtup]-[dtdown]).
CODE
Public Function f_fTime2Str(datTime As Date) As String
    Dim lngMinutes As Long, lngHours As Long, lngRemMinutes As Long, strMinutes As String
    Dim lngAbsHrs As Long
[color="green"]
    'Function converts a date/time value to a string; Optimized for speed 04/16/06; BENCHMARK: 18.6 MicroSec (PGDF)
    'Example: fTime2Str(#1/3/1900 4:53:00 PM #) = "112:53"; Useful for apps that deal with accumulated time values
    'lngMinutes returns the total number of minutes in the date/time value "datTime"
    'lngHours uses the integer division operator to divide lngMinutes by 60 and return the number of hours
    '     (integer division gives us a significant speed increase over the normal "/" division operator
    'lngRemMinutes uses the MOD operator to return the remainder of lngMinutes/60 (minutes)
    '     MOD is the fastest way to do this. (305 MOD 60) results in 5
[/color]
    lngMinutes = 1440 * datTime 'Total number of minutes in the date value
    lngHours = lngMinutes \ 60 'The number of full hours in the date value
    lngRemMinutes = Abs(lngMinutes Mod 60) 'The number of minutes left-over
     [color="green"]
     'add a leading "0" where minutes or hours is less than 10
     [/color]
    If lngRemMinutes > 9 Then 'Test for this first because of the 60 possibilities, 50 have 2 digits
        strMinutes = ":" & lngRemMinutes
    Else
        strMinutes = ":0" & lngRemMinutes
    End If
    If Not Sgn(lngMinutes) Then  [color="green"]'Its 0 or a positive date...Sgn(x) -1 for a neg, 1 for a pos, 0 for a 0
  [/color]
        If lngHours < 10 Then
            f_fTime2Str = "0" & lngHours & strMinutes
        Else
            f_fTime2Str = lngHours & strMinutes
        End If
    Else  [color="green"]'here we deal with negative time values  [/color]
        lngAbsHrs = Abs(lngHours)
        If lngAbsHrs < 10 Then
            f_fTime2Str = "-0" & lngAbsHrs & strMinutes
        Else
            f_fTime2Str = lngHours & strMinutes
        End If
    End If
End Function
merlicky
Change
Private Sub PartType_AfterUpdate()
Me.Part.Requery
End Sub
to
Private Sub PartType_AfterUpdate()
Me.Requery
End Sub
and see if that works
jmcwk
Thank You I will take a look at that, this db is long from being completed still as you can tell from this thread you might want to look at the Calendardemo it pretty neat as well.
jmcwk
Same effect do not get the status bar comment however the Parts do not change regardless of the Part Type
Tomolena
John, forgive me but I'm having a little trouble following what's happening here. I'm running your project on my desktop now. What exactly are you doing when you get the original "Error" message that you referred to (I can't seem to reproduce it) Are you starting a new record? tabbing into the subform? updating the PartType combo?
merlicky
Okay...Here's the dillio...
The relationships in your query are what is causing the problem.
The simple solution is to go into the query design mode and delete the relationship between tblParts and tblPartsType. Then you need to add a relationship between PartTypeID in tblPartType and PartTypeID in tblLogDetail.
This should take care of your problem.
jmcwk
Jack, If uou are lost I am DOOMED! anyway yes your attachment shows correctly however change the recordsource from the tblLogDetail to the qryLogDetail
jmcwk
Updating the Part Combo
Tomolena
It doesn't seem like the PartTypeID field should even be in tblLogDetail. Why isn't that redundant? Isn't PartTypeID dependant on PartID which is already in the table?
Jack Cowley
John -
id you try the solution presented by melicky? I have not looked at the query or anything else, only the two combo boxes... I am going bonkers trying to find a problem with a db of my own and I am about to lose my mind...BUT I think I may have just found the problem... Anyway, if merlicky's suggestion does NOT fix the db let me know....
Jack
jmcwk
Unless I am doing something drastically wrong (more than likely the case) your solution did not work I get repeating records.
merlicky
Oops...I forgot one part...

In the query you've got to change the table for the PartTypeID field from the tblParts table to the tblLogDetail table.


EDIT: see added attachment
Edited by: merlicky on Wed May 10 18:54:26 EDT 2006.
jmcwk
Tommy,
took it out and it made no difference.
Tomolena
Is it just me? Am I loosing my marbles here?
merlicky
Tomolena...
No, I presented an easy fix...the best way would probably be to restructure the tables to remove the PartTypeID from the tblLogDetail and have the PartType combobox unbound with the default value based on the Part. Then you could still limit the Part combo's list based on the selection from the PartType combo...If I'm making any sense.
Tomolena
I was chastised recently for offering an easy fix to someone who was under a little time pressure. John is in the early stages here and it was driving me buggy that people were already offering him ways to work within a glaring design defficiency that was apparently being ignored. I didn't mean to be critical. I was just wondering about my own assessments at that point.
jmcwk
Tommy,Merlicky,Jack
ppreciate everyone comments very much have come to the conclusion that I am starting at square one Structure wise etc. get it right now rather than not and creating problems down the road. I do not think it will take all that much PartTypeID will come out of the tbllogdetail for sure and a few more minor things I do believe that those two tables are the biggest problem at this point.
Thanks To You All again frown.gif
Starting Over Now!
Jack Cowley
Tomolena -
You are right in not subscribing to a work around. I just now came back to this and the only error that I see is in tblLogDetails. He has PartTypeID there and PartType is a 'lookup' for tblParts and does not belong in tblLogDetail. PartID is all that he should need in the table.
Do you see something other than that? Am I missing anything? I was trying to help the OP set up his cascading combos' and did not look beyond that, which I should have done... My excuse is that I was trying to find a piece of code that was causing everything to go belly up... I am sticking with that excuse!!!
Jack
Jack Cowley
John -
took a very quick squiz at your table tblLogDetails and the only problem I saw was PartTypeID. It is a 'lookup' for your table tblParts so it does not need to be in the table. If you look at the table the single record has a PartID of 51 and a PartTypeID of 9, yet in the tblParts part 51 has a PartTypeID of 3...
Other than that I think you are OK, but I did NOT spend a lot of time looking at the table and your relationships...
Jack
jmcwk
Thanks Jack I agree with you like I said I am both rethinking and relooking at the structure again I do know for a fact that PartTypeID WILL be coming out at he least.
Jack Cowley
John -
ormalization is a stinker and it can get my head spinning with the best of them! Once you clean up that little bit in the table your combo boxes should work just fine. I am not sure of the purpose of tblLogDetails, but if you are assigning a PartType there then you do not need PartTypeID in table Parts. You will have to sort out here the PartType goes and if it belongs in tlbLogDetail then remove it from tblParts....
Jack
Tomolena
I don't think your missing anything.This is a vry basic star schema that shouldn't be giving him any problems. If it were me, I'd correct the redundant field problem and start from scratch with a new form; Then code the combos. That just seems so much easier than trying to track down a conflict resulting from "error creep."

Anyway, I'm very glad to hear that I'm still sane grin.gif
Jack Cowley
Tomolena -
HEW! I am glad that you don't think I am missing anything as it has been a wild ride today. I believe I would miss an elephant in the chair next to me....
Jack
jmcwk
Jack,
Have a glass of your favorite vino on methat will fix you right up!
Tomolena
Well...You DID ask...didn't you? smirk.gif
Jack Cowley
Tomolena -
did ask and I DO appreciate your response!!! When you get to be my age you are unsure of a lot of things and you appreciate all the help that is given!!!
Jack
Jack Cowley
John -
beat you to the punch on that one. It has been a long, hot day and a chilled glass just hit the spot...
Jack
Dial222
Man, didn't this one just go the whole hog! Hope ya got it solved by now (09:30 BST).
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.