Design Question/Problem with Prompts

[glynch123]

Hi,

I am fairly new at Access and have a question: I have a "Project" database. Each Project has Components. The Components are categorized as follows:

Category (i.e. Lights)
|
SubCategory (i.e. Square, Round Lights)
|
Component (i.e. 5 inch to 10 inch, 11 inch to 15 inch)

I have a form where Components are selected for a project. The form has two subforms. In one form, when a Category is selected, the SubCategories are displayed there as buttons. When a SubCategory button is pressed, the corresponding Components are displayed in the other subform. To display Components of a SubCategory, I have a table created called ComponentsList. Before selecting a SubCategory, the current records in the ComponentsList are deleted. The Components for the selected SubCategory are then added to ComponentsList table through an SQL call and the form displaying the Components calls a query to this table.

Is this the way I should go about this? Is there an easier, better design approach? Currently, my problem is that when I add/delete records, I am prompted for Yes or No to proceed. If I am doing this correctly, how can I suppress the prompts?

Thank you!

 

[jimb0ne]

try using this line to turn off system messages:

setwarnings false

then to turn them back on do the same, but pass a true value.

 

[glynch123]

Hi, jimbOne.

This works! Thank you. But is this a common practice? I wouldn't want to hide important warnings. Thank you.

 

[Ed2020]

I assume that you are using DoCmd.OpenQuery to run the SQL statements.

Rather than using DoCmd.SetWarnings use CurrentDB.Execute, this will run the query or SQL statement without having to turn warning messages on/off.

Ed Metcalfe.

Please do not feed the trolls.....

 

[RoyVidar]

Yes, the turning off and on (after usage) is common when using the runsql method of the docmd object.

[tt]docmd.setwarnings false
docmd.runsql sSql
' or other message generating code
docmd.setwarnings true[/tt]

What you might consider, is another way of displaying your component list, cause deleting/adding reocords causes db bloat. When records are deleted, they will still be within the db, until a compact/repair is performed, so if it's possible to get your list thru queries/linking etc in stead, you might get less headaches later on;-)

Roy-Vidar

 

[KenReay]

Hi

alos paoosibly of interest to you to suppress messages in bound forms:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[glynch123]

Wow, thank you everyone for all of your help! That was really fast.

Ed2020: I was using DoCmd.RunSQL. CurrentDb.Execute works great! Thank you very much!

RoyVidar: Thanks for the info. I will have to modify that! I tried using queries before but I couldn't get it to work for what I wanted. I might repost with a question along those lines.

KenReay: That is a neat solution. Thank you!

I appreciate your time! Thanks again.

 

[Ed2020]

Personally I prefer the CurrentDB.Execute method because:

1. It's less code (and therefore less typing and marginally more efficient.

2. It supports both ignoring and trapping of errors.

3. It supports transaction processing.

4. If the code errors out after you have turned warnings off you don't have to worry about turning them back on (although this is being fairly pedantic - it's not a big deal).

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Personally I prefer the CurrentDB.Execute method because:

1. It's less code (and therefore less typing and marginally more efficient.

2. It supports both ignoring and trapping of errors in the SQL execution.

3. It supports transaction processing.

4. If the code errors out after you have turned warnings off you don't have to worry about turning them back on (although this is being fairly pedantic - it's not a big deal).

Ed Metcalfe.

Please do not feed the trolls.....

 

[new2ora]

Is there a way to turn off these warnings permanentaly for one database?

 

[KenReay]

docmd.setwarnings False

and to reverse

docmd.setwarnings true

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK