Help with a complicated IF, or another way of doing it... (1 Viewer)

[Johnsonsimon]

Hello all... again,

I am trying to get something to work using VBA, but I am really struggling. I presume it can be done, but I thought I should defer to the experts to find out.

The DB is a booking system for a chauffeur company. The are numerous tables, but the important 4 regarding my question are as follows;
Jobs & Booking
LocationMaster
LocationCollection
LocationDropoff
(The location tables all use LocationID primary key. The Jobs & Booking table has fields Called Collection Location and Drop-off Location which link via Foreign keys to the respective tables LocationCollection or LocationDropoff)

To add new locations or update current ones the user opens a form which links to the LocationMaster Table and then upon closing the form it runs some action queries...

Basically I have Append and Update queries that run to append or update the details from the LocationMaster table to the LocationCollection and LocationDropoff Tables.

The issue arises when I try to delete certain records from the LocationMaster table, if they are referenced in Records on the Jobs table. I have tried using a Delete Query which semi works, but often leaves records in one table intact, but deletes them from other tables (i.e. if the location has been selected in a Job as a collection and never used as a drop off location the delete query will delete it from the dropoff table and master table but leave it in the collection table.).

I have tried to use some VBA and an IF statement to get it to check if the LocationID is referenced in either the Collection Location or Dropoff Location fields of the Jobs & Booking table in any of the records, and if it is then to stop END the VBA but if it isnt referenced anywhere then the record will be deleted from all 3 tables (LocationMaster;Collection;Dropoff).


Here is the code, which obviously doesn't work, but I am not adept enough at VBA to even begin to figure out what I need to do. So thats where you come in..... Please help

Cheers Simon

    If (Eval("6<>MsgBox(""Are you sure you wish to delete this Location?"" & Chr$(13) & ""Please Note: If this location is used in a current, completed or cancelled job it will NOT be deleted."",52,""Delete Location"")")) Then
            End
        Else
            If Me.[LocationID] = [Tables]![tbl_Job]![Collection Location] Or [Tables]![tbl_Job]![Drop-off Location] Then
            End
            Else
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "Delete Query - Collection", acViewNormal, acEdit
            DoCmd.OpenQuery "Delete Query - Dropoff", acViewNormal, acEdit
            DoCmd.OpenQuery "Delete Query - Master", acViewNormal, acEdit
            MsgBox "The selected location has been deleted." & Chr(13) & "It will still be displayed here until the next time you open this form." & Chr(13) & Chr(13) & "Please Note: If this location is used in a current, completed or cancelled job it will NOT have been deleted.", vbInformation, "Location Deleted"
            End If
        End If

 

[vbaInet]

The function you can use is called DCount(). Use that to check within the two tables for records that match the LocationID. If DCount returns anything greater than 1 then take necessary action.

Here's info re DCount():

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[pbaldy]

I'm curious why you have the

LocationCollection
LocationDropoff

tables. I've written applications for both limousine and taxi operations. I used a single locations table. My booking table would have similar fields for the pickup and dropoff locations, but both would be related to the single locations table. In queries you simply alias the table to get the second instance of it; you don't need two separate tables.

 

[Johnsonsimon]

The reason for the two tables was because of an issue I was having with a report.

The main report, or Job Sheet as they refer to it as, has both the collection and drop off locations on it. the report is based on the Jobs & Booking table, so therefore the only data it displays as standard are the forgeign keys. When I went to add the fields from the Location table it would only ever show up one of them I couldnt figure out how to get it to show both sets of data i.e. collection and drop off locations, so I created the two tables and then created the Master one so I could some how consolidate the info in them so it was always the same.

If you can tell me how to get around this in the report then I can do away with the 3 tables and just use 1.

Cheers

Simon

 

[vbaInet]

I'm sure Paul has some more insight on this. It's possible for there to be multiple drop off points and in such a case how do you record that? And it's also possible to have multiple pickup points too so how would you record that too?

I guess it's a matter of style. Some people may prefer to have split tables one for collections and the other for drop offs and some (like myself) would opt for one table (as Paul indicated) with a field that identifies if it's a drop off or a pick up, i.e. one record per pickup/drop off. I don't see how you weren't able to display the drop off information on your report though.

Did you get the DCount to work by the way?

 

[pbaldy]

I suspect Simon has a similar situation, but I'll speak only for myself. The Locations table (we call them landmarks) is not a transaction table, it is simply a list of frequently used locations and their addresses (a lookup table). It contains a key field along with various info about the location (name, address, etc). It enables dispatch staff to quickly choose a landmark and have all the related info pop up.

In my case the applications did not require accounting for multiple pickup or dropoff locations. The reservation table simply has fields for the pickup and dropoff landmarks (or full address info, since many are one-time addresses).

Simon, I did tell you: "In queries you simply alias the table to get the second instance of it". Start a query and add your booking table, then add the main locations table twice. You'll notice that Access aliases the second copy, like "Locations_1". You join your pickup location in the bookings table to one of those instances and your dropoff location to the other. That enables you to return fields from both separately, even though it's actually one table. I change the alias and add one to the first instance so they're self-documenting. In SQL view it would look like:

FROM Bookings INNER JOIN Locations AS FromLocations ON Bookings.PickupLocation = FromLocations.IDFieldName

 

[Johnsonsimon]

Thank you so much to everyone. I have not had chance to try these options out yet, but I suspect with the advice I will be dropping the 3 tables back to just a single one as it will solve A LOT of the problems I have been having, now that I know how to do so.

If it doesn't work I will try the DCount(), but I am glad to know this function anyway.

So thank you to all.

Yes Paul, the situation with the tables for Locations is as you suspected. And Yes there is no need for multiple addresses/locations, just the initial ones as additional addresses go into a 'Job Notes' field in the Jobs & Booking Table.

Gracias, muchas gracias amigos.

 

[pbaldy]

De nada, amigo!

 

[Johnsonsimon]

Paul,

It appears my thanks were a little premature. i am sure the advice and direction you have given is spot on, I am just a little thick!!! HaHa.

I have added a second instance of the Locations table to the query (it is an embedded query, will that make a difference), I then link the Primary keys from each instance to their respective Foreign key in the Jobs Table. On the report I then added esxisitng fields and added both lots of instances, so I have Location.LocationName, etc.. and Location_1.LocationName, etc.... but when I run the report it just puts the data from the the first Location, ie. the collection, in both boxes. I went into the row source for the fields I had added for Location_1 but as soon as I press Ctrl+S it reverts from Location_1.Whatever to Location.Whatever .

I hope you can provide some clarity... and thank you for the help again it is really appreciated.

Cheers

Simon

P.s. The DB is split to a FE/BE scenario I hope that won't make any difference with any of this.

 

[Johnsonsimon]

Right... ignore me, I am thick lol. I just built a query as you said and then based the report on that query and it works great.. Thankyou.

gracias por su ayuda

 

[pbaldy]

No problema!

 

[Johnsonsimon]

HELP HELP HELP

Sorry Paul... following you advice on the query front I decided to Base ALL of my forms and reports on separate queries rather than embedded. I now have just 1 loactions table which is in my Relationships window twice as LocationMaster and LocationMaster_1, each of the Primary keys in these tables is linked to the repsetive foregin key in the Jobs table.

I changed the record source of the Jobs table to the query.
Initially, when in Form view I was unable to edit any of the records or add new ones.
Following something I found on another site I changed the recordset type from Dynaset to Dynaset (Inconsistent Updates), which allowed me to edit and add new records....

Now here are my problems;
- When I add new records, leave the form and go back, the new ones arent there, and it also appears that whatever the last record is I have sleeted, when I go back it is no longer there. It does appear to be a query issue, rather than form related as I also see the same when I run the query. If I add a new record in the Jobs Table, it doesnt show up in the query, but if i add in the query it works the other way round.

Also on the Jobs form, previously I had a combobox which listed all the CLIENTS and then when chosen it saved the CientID to the ClientID field on the jobs table/form. I also have the Address, Telephone number, email etc... fields from the CLIENT table on the form, which used to populate automatically when you chose a CLIENT from the combobox, but now it doesn't update.

I have a update query which changes the StatusID of the Jobs from 1 (Active) to 3 (Completed) when the Job date is <Date(). The query is running fine, but the results are not showing in the queries.


Where am I going wrong, I am so confused!!! I really hope you can help. I cant see where I have gone wrong, the only major change has been to delete the other 2 location tables and just use 1 Master table instead.

I also went on your site and followed the advice for Cascading combos, which do appear to work brilliantly!

 

[pbaldy]

Can you post the db here?

 

[Johnsonsimon]

Hi Paul,

I dont know where I went wrong but I went back to an old version I had saved prior to dropping the 2 location tables and then reworked from there and just used the dcount function, keeping my 3 location tables, and all is now working how is required so hopefully we can stick with this layout.

thankyou for all the advice.

simon

 

[pbaldy]

If you're happy I'm happy. Glad you have it working.