Solved

MS Access count msgbox.

Posted on 2010-09-23
8
416 Views
Last Modified: 2013-11-28
Hi I will try to explain as simple as i can. I have 4 tables and I got four checkboxes(1,2,3,4), one load button , one delete button on the form.
 tables A --> B--> C-->D. ( linking order)
So table A is the parent table of all four tables. So when the checkbox 1 is checked. it should delete all the data in table A.  But if the linked tables has got data in it (table B,C,D) then . it should display the count of the records in the linked tables.
So the parent table delete the data based on the count in the linked tables. I have to count the tables twice before any checkbox is checked and after each delete is performed on a linked table to have the most current total.

I can do this through VBA. But It is getting bigger in size. I am not much familiar with the functions. So I was thinking can I create a function module which just takes the count of all tables . then I can call that function wherever I can and use its variables in the message box to display the actual number.
0
Comment
Question by:vihaan
  • 4
  • 4
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
If you delete the main record you may lose the corresponding Child records if you have cascade deletes turned on.
Either that or you would get a message that you cannot delete the Parent record if it has related child records...
So I am a bit confused by the request.
In the first scenario, if you delete the parent record, all child records would be deleted, hence no child records to count...
(or am I missing something here?)

So, can you give us a bit more detail on this rather unique request?

;-)

JeffCoachman



0
 

Author Comment

by:vihaan
Comment Utility
No I did not turned on the cascade deletes. So you will have the child records.

1st scenario:
the thing is that if parent table checkbox is checked and linked tables are not checked. So when you hit the delete button on the form . its gonna display the count of the linked tables failing the delete. So here I would do the count of linked tables before any checkboxes are checked.
2nd scenario:
if the parent table and the linked table is also checked. Then as i explained in the above scenarion, I would have the count on all the linked tables. Now when i hit the delete button , the linked table would delete its records because the linked table checkbox is checked and here i should do the count again to update my linked tables count. because the parent table performs the delete function based on th count of linked tables.Parent table would have a successfull delete if the linked table count is 0 and its fails if the count is  >0. I can do this through VBA but as i said it is getting bigger in size.
I was lookingto use function module which have take the count of all tables. and i can  call it wherever i can and use its values in message box. If you did not understood. I would explain in a most simplistic manner. Thanks for responding.
0
 

Author Comment

by:vihaan
Comment Utility
Do not think i was being rude on reading the line "I would explain in a most simplistic manner". I meant i would explain it again. Thank You.

P.S. As I was going through it , i thought that line was more blunt .
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Perhaps if you posted a sample of your database another Expert may be able to assist you further...

Sample database notes:
Back up your database(s).
Combine the front and back ends into one database file.
Remove any startup options, unless they are relevant to the issue.
Delete any objects that do not relate directly to the issue.
Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
Compile the code. (From the database window, click: Debug-->Compile)
Run the compact/Repair utility.
Remove any Passwords and/or security.
Post explicit steps to replicate the issue.
Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.

JeffCoachman
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:vihaan
Comment Utility
one more question Can you use the variables declared in Function module . Use it values and display those values in a event procedure (like on current event) in a message box. If you can , how should i do it .
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
You can put the Function in a module and make it Public:

Public Function GetMyVal()
...

Then call the Function from anywhere in your app to retrieve this value:
    me.txtval=GetMyVal()
OR
    "SELECT * FROM Yourtable WHERE Age=" & GetMyVal()


Or you can create a Public variable, and this value can be set or referenced, in code, from anywhere in your Project.
Public lngMyVal as long

JeffCoachman
0
 

Author Closing Comment

by:vihaan
Comment Utility
good
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now