General Question

[gheyman]

Can you take a calculated field, on a form, and store it as a Value in a Table (preferably as a Primary Key)?

 

[Joe4]

Similarly to what I showed you yesterday here: http://www.mrexcel.com/forum/micros...-value-column-seperate-table.html#post4710052
you do not use [ or ] in the D functions (DMax, DCount, DLookup).

And if [PROGRAM NAME] is a Text field, you need text qualifiers to go along with it (single or double-quotes), i.e.

[COLOR=#333333]=DLookUp("PROGRAM ID","PROGRAM_LIST","PROGRAM NAME='" & [Combo18] & "'")[/COLOR]

assuming that Combo18 is pulling the correct PROGRAM NAME value.

However, on your Form where the user is making the selection, you do not need to show any calculations here. Just have them pick the values that they need to pick, and all the calculations (lookups, concatenations, etc) can be done on the VBA side.

I am two days into learning Access

You mentioned this in your previous post. Are you familiar with Relational Database concepts (Rules of Normalization, etc) and VBA?
The reason why I ask is because Access is not the most intuitive program and the learning curve is steep, especially if you have to design a database. It is really not something you can expect to "learn-on-the-fly" - there is just too much to learn and it is very easy program yourself in a corner very quickly.

I speak from experience. I tried to design my first database (and it was a complex one) with very little knowledge of these topics. I kept spinning my wheels and grew very frustrated. After 6 months, I talked to an experienced Access programmer, and they suggested that I read up on Relational Database Theory, and take some classes in Access and VBA. I did that and soon realized that most of what I did was wrong and had to start over from scratch. However, the second time around, I was able to create a well-designed database. 15 years later, they are still using it to do billing.

 

[gheyman]

Thanks, Joe.

I am a slightly above average Excel user with Some VBA experience. This is all relative - compared to you moderators. I have started reading some Access books and going through YouTube. The truth is I am trying to build a software program for use in the DOD Manufacturing industry. I was working with programmers using C++ but that's not working out. I am pretty sure everything I need to do can be done in access and possibly excel. I really need to find a Programmer to work with.

 

[Joe4]

I really need to find a Programmer to work with.

Yeah, the on-line free help might not be able to give you the level of assistance that you need. It works best with specific questions but a particular task, but it looks like you have a lot of development questions. You might want to consider hiring a Consultant to help you. MrExcel offers Consulting Services. To be honest, I am not certain if they have any Access specialists on hand in their Consulting Services (I think they might), but it may not be a bad idea to inquire, if you are open to that. MrExcel.com | Excel Resources | Excel Seminars | Excel Products

 

[gheyman]

If they don't have Access Specialist, you open?

 

[Micron]

I am taking these parts/pieces and Concatenating them (Hence the Calculation).

If I get the drift, this is not a calculation and calling it that is going to result in some correct but non-applicable advice about calculated fields. What you're doing is, as you correctly noted, is a concatenated value. Yes it is very possible to enter this value into a text data type field in a table as noted in post #5. However, IMHO the entire form does not have to be unbound, just the concatenated control, or any others like it. At some point, you'll have to append a record to your table which means the concatenation has to be complete or you will not be able to navigate off that record. I'd reconsider the concatenation as a PK, using an autonumber instead, and making the concatenation both indexed, no dupes, and required.

 

[gheyman]

Thank you. This is helpful

 

[Micron]

I am trying to build a software program for use in the DOD Manufacturing industry.

My 2 cents on this comment. You realize that with all of the measures you can take to make your db secure, it never truly will be? At it's best, Access is less secure than the least secure program. Just throwing that out there in case security and privacy is a major concern.

 

[gheyman]

Thanks, not a concern right now but good to know for the future. Kim plans go as planned this will reside on the customers server.