Combo Box Update ... after user input!

[lgeer]

I have a field called [no_units] in my tap_audit_frm. I have a combo box just under it that sql-queries [total_cost] based on no_units*price.

Q) I'm trying to requery combobox after updating [no_units] user input (combobox idea is to have the [total_cost] hidden from general public until I need to see it - the combobox provides two things - generic security of that field and the requery) but I can't get it working.

I tried ... onchange [no_units] cbototal_cost.requery

Any help will do
I'm lost ...

Tinker

 

[ColinEssex]

Hi

Not 100% sure what you mean but you could try

Me.Refresh onExit of the field

I may be wrong though.

Col

 

[aphelps]

Try this, in after update event:

Private Sub no_units_AfterUpdate()
no_units.Requery
End Sub

I have a similar situation except that mine produces a unique dropdown list in a field based on the value of another field--this one is modified from that, so it may not work, but it gives you a place to start.

Good luck.

 

[lgeer]

Didn't work?

I have no clue why? Maybe I'm querying wrong?

 

[Elana]

If you're trying to requery the combobox cbototal_cost, then you need to requery it After Updating the no_units field.

After Update Event on the No_units field:

me.cbototal_cost.requery

 

[lgeer]

Didn't work?

Should the combobox total_cost be unbound?

 

[Elana]

Are you getting an error message?

Also, please confirm what the name of the combobox is -

 

[lgeer]

Sorry about confusion ...

Once I enter a # of units [no_unit] I'd like it to update my [cboTotal_Cost] combo box - meaning if I go back (after initial data entry) and re-enter some new units [no_units] it would then requery the data and place the results into my combobox [cboTotal_Cost] then save that data back to the table in those fields. For some reason the data isn't getting saved back to the table?

Tinker

 

[Elana]

Okay, is any value at all being saved after you perform the calculation?

I don't quite understand why you're using a combo box to hold a calculated value. Also, FYI it is usually not a good idea to store a calculated field in a table.

Can you post your database so I can look at it?

 

[lgeer]

Here you go ...

I have people in and out of my office and it's OK if they see the cost but I'd like the total cost (to me) hidden. 1st I made a query of the data I'd like calculated, then I pass it to the form using sql.

If there's a better way - feel free to let me know! 'm very new at this and learning ... from a book.

Tinker

 

[lgeer]

One more try ...

Sorry about that ... I have alot to learn about this program! I thought that compacting it would perminently change it? Have to read up on that ...

Here you go ... it's a simple dbase that will lead into more as we get this project underway.

 

[Elana]

Hi - I did some tweaking to your form. Take a look and let me know if this meets your needs.

I have a couple of other observations that I think you should consider when you're designing this database.

1. You should have a separate Projects table whose primary key is an autonumber field called ProjectID. In this table, you can store as much info as you want about each project.

In your tap_audit_tbl, the primary key should be changed from a text field (Project Name) to an autonumber field (called AuditID for example). You should then insert a field in this table called ProjectID that is numeric. Join the audit table and the project table together on this field in the relationships window.

2. You need not and should not store the result of a calculation in a table. It is poor design and generally not necessary. To quote another Access forum contributor (Fornation):

"Storing calculated values adds extra bulk to your database when its not essential - you have all the elements required to recreate the answer to the calculation, and in most instances it is beneficial to do it this way in case any of the base data has changed since the last calculation. Using queries anbd controls for calculations is much more efficient as they are not stored and the results exist only for the life of the form or report.

There are a few instances where you might need to store the value, i.e.previous years invoices with a changing per unit price scale but that is usually worked around by using date specific pricing tables."

If you need more information on this topic, study up on Database design and normalization. Good luck!

HTH

Elana

 

[Elana]

Here is the revised db.

 

[lgeer]

THANK YOU !!!!

That was it ... and thank you for the information ... makes sense not storing data that you can always calculate ... so do you need to identify the total_price field or can you just assign it in the query as a calcualtion? It seems to me that you wouldn't need that field?

Anyways - thank you very much for the help ... I have learned allot from this!

One other question ... what books do you recommend for Access? I have "Access 2000 - The complete Reference" and they use a sample dbase to teach stuff ... a bit confusing! I value real world users opinions over "book readers"!

Tinker

 

[Elana]

You create a field called total_cost any time you want to know the result of a calculation, in a query or in code...

Re: Access reference books, I have the Access 2000 developers handbooks ( Publisher: Sybex /Authors: Getz, Litwin, Gilbert). I also have a good VBA book called Access 2000 VBA handbook (author Novalis).

I would search Amazon.com or BarnesandNoble.com to find books that would be a good fit for you.

Good luck!

Elana

 

[lgeer]

Thanks again ...

I tried your suggestion (in a new test) and I can't get the data from the query to pass to the form now? When they were in the same table ... it was no problem, now it can't find the data? I tried using the expression builder but it gives me a #Name? <error> I have alot of reading to do. The other one works but I'd like to understand what all the hoopla is on relationships ... once you st the relationship the data should automatically be linked ?right? I'm lost on this one.

Tinker

 

[Elana]

Okay, post your mdb again and I'll show you -

 

[lgeer]

You lost me at ...

why I have to seperate the tables? Would it not be easier to search one table rather than several and having to set all those relationships? I don't understand the need for relationships?

Here's the one I was testing ...

Tinker

 

[Elana]

If you don't understand the need for "all those relationships," I'm afraid I'm going to have to suggest that you read up on database design before we go any further.

 

[lgeer]

Per my the book I mentioned before ... relationships are to enforce referential integrity ("a set of rules that attempts to keep the database complete and with no loose ends.") I understand the reasoning ... with "related" fields in each of your tables there's less of a chance to enter/store the wrong data at the users level. What's actually confusing me with the added fields is tracking them ... I guess that's why they created the relationships view window? (i.e. Do I manipulate the customers_name or customersID) This is where putting it on paper has helped but I still get confused).