DLookup or other solution???

[Kevin_S]

Hi everyone

I have a question about DLookup and wether or not it should be used in this case. I have a pretty complex table structure in the db so for this sake of this example lets just say I have a db with 3 tables: 1 Parent table and 2 children tables - one child with many to one relationship and the other with a one to one relationship. I'm trying to check 2 variables to see if they meet a certain requirement. Here is the present code (slimmed down for the example)

Dim HCP As String
Dim HCT As Boolean

HCP = Nz(DLookup("[HC_plate]", "BAS_SkidPier", "SITEID='" & [Forms]![frmMain]![SITEID].[Value] & "'"))
HCT = Nz(DLookup("[HC_Toilet]", "BAS_toilet", "SITEID='" & [Forms]![frmMain]![SITEID].[Value] & "'"))

If IsNull(HCP) Or HCP = "" Then
Exit Sub
Else
Me.txtHC_plate.Value = HCP
End If

If IsNull(HCT) Then
Exit Sub
Else
Me.txtHC_Toilet.Value = HCT
End If

At this time I have put two unbound fields (txtHC_plate and txtHC_Toilet) on the form just to see if I am capturing the correct data for the current record on the form (frmMain).

Now - the problem: The DLookup function always works with the one to one table as there is only one record BUT for the table with the one to many join it finds only the first record and then uses that for the value. How can I set this up to look at all of the related values on the many side? Do I need a loop?

Any and all help on this is appreciated.

Thanks -Kevin

 

[RichMorrison]

Kevin,

DLookup won't help you flush a bunch of toilets (I couldn't resist )

It sounds like you need to know if there is at least one toilet
and
what all the toilets are.

For that use a RecordSet. Make a string that is the equivalent SQL statement to the DLookup parameters.

Check the number of records in the RecordSet. If greater than zero then use a ReadNext loop to get all the values.

HTH,
RichM

 

[Kevin_S]

Hi Rich, thanks for the reply:

OK. I thought I had a firm grasp on creating recordsets and loops but evidently I don't as I am having a bugger of a time getting this to work. Here is what I have so far:

...................................................................................................
Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim intHCT As Integer

Set rs = CurrentDb.OpenRecordset("SELECT HC_toilet FROM BAS_Toilet WHERE ((BAS_Toilet.SITEID)=[Forms]![frmMain]![SITEID])")

intHCT = rs.RecordCount

End Sub
.................................................................................................

All I am trying to do at the moment is to get the recordset to populate and then count how many records are returned. However - I think there is an error in the SQL statement as I am getting an error (3061) when I try to check the number of returned records.

I have a feeling that the reason for this is that this code lies in the 'OnCurrent' event of a subform and the recordset I am trying to create is dependent on finding records in the table (BAS_Toilet) that match the current parent forms ID #. Another possible reason for the error is that not all records associated with the parent form/table will have a record in the child form/table - thereby created a null value in the recordset... If possible, could you provide some insight or a possible example as to the means by which to fix this problem?

Thanks again Rich!
Kevin

 

[Kevin_S]

Hi Pat,

Thanks for the input!

Unfortunitly thats not all Im trying to do... Basically, What I want to do is create a records set comprised of values associated with the Primary Key in my main form (frmMain). The structure between the tables in 1-to-many. I was trying to use DLookup to get the matching record returned but this finds the first associated record and not ALL of the records. What I need to do is (I assume):

Create a recordset based on the following SQL Statement:

Dim rs As DAO.Recordset
Dim intHCT As Integer

Set rs = CurrentDb.OpenRecordset("SELECT HC_toilet FROM BAS_Toilet WHERE ((BAS_Toilet.SITEID)=[Forms]![frmMain]![SITEID]))"

Then check and see if the recordset has more than one record returned:

rs.MoveLast
rs.MoveFirst
intHCT = rs.RecordCount

If it does have more than one --- then (this is the part that is really confusing to me!!!!) Loop through the record set and look at each related record to see if any of the related records has the boolean value of 'True' under field HandicapedToilet.

If rs.recordcount > 1 then

'Loop and search for HC_Toilet = true in related records

else
....

Do you think you could help me refine this? or possible point me in the right direction? I have been searching now through this forum/MS Access Help/ and my Developing VBA for MS Access book all morning and still haven't grasped what I need to do


THanks Pat,
Kevin

 

[Kevin_S]

Ok - I'm still not being clear

Here is what I want to have happen:

I have a field called (tckFULLAccessible) on subform "frmAccessible". In order to have a site listed as full accessible it must have 3 other variables in 3 different tables checked off as true. What I was trying to accomplish is that on the 'OnCurrent' event of the form - to pull in that values of those three other table fields (HC_Toilet, HC_SkidPier, HC_Parking) and if those three field = True - then to set the value of tckFULLAccessible to = True.

Now , this would be simple enough if it were all 1-to-1 relationships and could easily be accomplished with the DLookup Function to pull in the values from these tables where the PK(SITEID) is equal in each table.

However, the problem is that 2 of these tables (tblToiletInfo & tblSkidPier) are 1-to-many relationships and with more than one record for each related record in the primary table. I was attempting to use the DLookup function but it would only find the first related record where the ID's matched and then exit the function. - As a result of this --- I was getting instances where the code was telling me that there were no Handicapped Toilets at the site (when indeed there its - just that that record wasn't the first one returned by the DLookup!!!!

This is why I was trying to make a recordset to find ALL of the related records and then loop through them to see if any of the records had a value of True for HC accessibility.

Please Help!

Kevin

 

[Kevin_S]

As you said before "All I care about is that the criteria is met!"

THANK YOU SO MUCH!

I owe you so much for your kindness and help on this and many, many other posts!!!

Take Care,
Kevin