Multi Value Text Fields that allows users to key in data

[dhaugland]

Thanks - I am going to have to take some time to study what you did, but have a couple of quick questions.


1) I now have 2 seperate drawing forms. Are both required? It seems they are and I believe that "sfDrawings, is the sub-form on the origination (main) form. the form you created, seems to just be used for adding records to the drawings table? Is this correct?

2) On the main form (at the very bottom), when I click through the records, I need multiple times to get to the next CP. For example, if I have 10 drawings associated with the first, I need to click forward 10 times to get to the next CP. Is there a way to only click 1 time to get to the next CP?

3) regarding your question on revisions. the first revision is always A (or possibly 01)

4) Regarding the calculated fiels. I was thinking that I would need this to be able to search for unique combinations of drawing number & revision number. For example lets say change proposal (CP-2014-1) was written to change drawing #123 Rev A in May 2015 and then if we fast forward to December 2015 (and the original CP-2014-1) had not been completed yet, I would want the user to be aware of it so they could potentially add this new requirement to CP-2014-1 (and not create a new CP. On the other hand, if CP-2014-1 was already completed, I would envision that drawing 123 would have been changed to Rev B. As a result, we would want them to enter a new CP for drawing 123. Does this make sense??? In any event, If there is a better way to do this, I am open for suggestion.

5) regarding your final thought. I could have many scenarios. It may be possible to have multiple CP's that impact the same drawing/rev. It may also be possible that some of them are open and others are closed. I really dont' want to restrict these scenarios, but just make it easy for the user to identify all the possible combinations that may exist.

[CJ_London]

1) I now have 2 seperate drawing forms. Are both required? It seems they are and I believe that "sfDrawings, is the sub-form on the origination (main) form. the form you created, seems to just be used for adding records to the drawings table? Is this correct?

are both required? - yes - sfDrawings is for use in the CP_Origination form and updates tblLinks, the other is for adding new drawings to the tbldrawingdocs table

2) On the main form (at the very bottom), when I click through the records, I need multiple times to get to the next CP. For example, if I have 10 drawings associated with the first, I need to click forward 10 times to get to the next CP. Is there a way to only click 1 time to get to the next CP?

I didn't pick that up when looking at your db. I advised in post #9 'Assuming your 'trigger' is a CP the main form would have tblCPs as the recordsource' - you have this table plus tbl_links. Remove tbl_Links and any associated controls in the main form.

4) Regarding the calculated fiels. I was thinking that I would need this to be able to search for unique combinations of drawing number & revision number. For example lets say change proposal (CP-2014-1) was written to change drawing #123 Rev A in May 2015 and then if we fast forward to December 2015 (and the original CP-2014-1) had not been completed yet, I would want the user to be aware of it so they could potentially add this new requirement to CP-2014-1 (and not create a new CP. On the other hand, if CP-2014-1 was already completed, I would envision that drawing 123 would have been changed to Rev B. As a result, we would want them to enter a new CP for drawing 123. Does this make sense??? In any event, If there is a better way to do this, I am open for suggestion.

searching a calculated field is slow because there is no indexing. so search on the two fields that can be indexed (i.e. instead of dwg_doc_with_rev='doc+rev' you would have drawing_doc_number='docnum' and revision='revcode'. Also, your user might want to enter a document number to see a list of revisions from which to choose - again much simpler if the elements are kept separate. Not clear on your explanation of the rest of it! You may be better to split the drawing table in two, one for the number and one for the revision

[dhaugland]

Regarding my question#2 below

2) On the main form (at the very bottom), when I click through the records, I need multiple times to get to the next CP. For example, if I have 10 drawings associated with the first, I need to click forward 10 times to get to the next CP. Is there a way to only click 1 time to get to the next CP?

• (Ajax Response) I didn't pick that up when looking at your db. I advised in post #9 'Assuming your 'trigger' is a CP the main form would have tblCPs as the recordsource' - you have this table plus tbl_links. Remove tbl_Links and any associated controls in the main form.

I am a little confused what you mean by "Trigger", but I DO infact want the CP's to essentially be my main entity that houses all of the data (including) all of the drawing numbers that impact each. So for example, if the first CP has 10 drawings impacted and the second CP has 5 drawings, i need to be able to maintain that information about each of the specific CP's (first CP is associated with the 10 drawings and second CP is associated with 5). If I remove tbl_Links, won't I loose this tie between the 2 pieces of information?

[CJ_London]

'trigger' is probably the wrong word, I meant main entity. It's just the way my mind works

If I remove tbl_Links, won't I loose this tie between the 2 pieces of information?

no, not in the context of the way the form works - the linkchild/linkmaster properties on the subform control maintain the link (or tie) - so as you move through the cp's you'll see the drawings affected list changing

Don't be afraid to experiment. take a copy of your form as a backup, then make some changes and see what happens. Learning by trial and error, but better than just copying something and not understanding why it does what it does.

and to be clear, when I said 'and any associated controls in the main form.' I was not referring to the subform! I've just checked and the only one I can see is LinkPK. You can also remove the drawings_impacted control (and associated field in the table) since the subform replaces this

[dhaugland]

Hello there my friend - With your help and about 4 hours worth of trial and error, I think I am making some headway. I feal like you are my "sensei" and as such, have attached a link to my latest version for your review and critique.... (yes, still am blocked from zipping and sending)........
https://www.dropbox.com/s/s8oace3wtm0m8t9/CP%20Log%20Rev%20D.accdb?dl=0


My next steps are to get the drawing revision onto the subform with the drawing number and also see if I can swap out that calculated field like you mentioned earlier....... Once that is done, I am wondering if it would be possible to automacially have the new drawing number populate into the sfDrawings subform once it is added to the frm_drawings_docs form that you created for me last night (I need the user experience to be as easy as possible.......

If you have any suggestions on how to do this, that would be awesome. What is your profession and where do you live anyway? I am guessing it must be somewhere in Europe or somewhere close????

I cannot thank you enough for all of your help on this so far and am sorry if I am becoming a pain in the rump........ but I am becoming obsessed with this project and want it to be as user friendly as possible.

[dhaugland]

interesting, maybe the zip file actually worked. I had tried it, but thought it error'd out..........????

[CJ_London]

zip file worked!

I would need to know a lot more about your business model to give a focused response, however

get the drawing revision onto the subform with the drawing number

the answer depends on if you are going to include more information about drawings and revisions like a description (north elevation/2nd floor/landscape/access etc). if not then the short answer is the combo rowsource needs to be modified to include tbllinks linked to the drawing table so you can bring through the revision number - change the number of columns to 3 and you'll see the revision number in the dropdown. If you want to see it once selected, add an unbound text control and in the controlsource put =[drawingfk].column(2)

What is your profession and where do you live anyway?

I'm a professional developer in the UK with clients from small businesses to international companies. Wide range of different industries including hotels/spas/hair-beauty salons/event management/builders-developers/manufacturers/telecoms/utilities. Mainly bespoke but I'm developing a suite of very customisable integrated business modules for small business. Primarily use Access/VBA/Excel front end with Ace (Access), SQL server or MySQL backends. Also use other languages when necessary.

want it to be as user friendly as possible.

With regards your project, I'm a bit concerned you have 100,000 drawings with no apparent way of grouping them by project (or you have one mf of a project)- when you build to this number, the combobox just won't hack it, you'll need a smarter way to find the drawings. Just some ideas, but I see you have a 'cost responsibility' of customer - perhaps drawings should have a customer number in each record which is also recorded in the CP record - or perhaps customer should be the main form, CP a subform and links a further subform - then your combobox rowsource would be limited to drawings for that customer. Another thought is that drawings are dated, so a filter can be applied to only include the latest revision that predates the date of the CP.

Also, think about your combined drawing number/version calculated field - as a user, would you want to be adding in brackets, spaces and hyphens?

Another thought is automation - if the cp's come to you as a pdf or typed document, you can run it through an OCR process (optical character recognition) to populate your cp table - save a lot of typing.

[CJ_London]

re the combobox take a look at this link

http://allenbrowne.com/ser-32.html

[dhaugland]

Ajax - I want to thank you one more time as you have been EXTREMELY helpful and have already helped me take this WAY beyone my original question. As such, I feel like I am abusing your talents and taking advantage of your helpfulness. I will mark this as "solved". If there is any way to give you some positive feedback that could help you gain more clients, let me know.

Given this database that I am creating is to replace an excel spreadsheet, I am confident that I am well on my way to make this process 10X better than it is today, but I want to really rock them by providing a solution that is 100X better than the current one. You never know, maybe i can convince my management to hire you to help develop this database and really take it to the next level.

[CJ_London]

Happy to help!

Whereabouts in the world are you?

[dhaugland]

I live just about in the center of the north american continent. In the United States in the middle of North Dakota. I work for an Aerospace company that is actually headquartered out of North Carolina, but we have a pretty decent size plante here in North Dakota.

[dhaugland]

Hi Ajax - Hello from North Dakota!

CP Log Rev F.zip

I thought I understand these sub-forms to a reasonable level, but today when I tey adding another (Interchangeability impact), I am getting a "paramater" error". I don't suppose you could help me troubleshoot could you? I have attached a zip version of my file.

[CJ_London]

so far as I can see, your subform has a linkchild property of CPFK, but this is not in your Interchangeability impact table

[dhaugland]

Do you think I even need this link child property in my interchangeability sub-form since all of the fields on the table are simply yes/no check boxes? Wondering if there might be a better way to tie it all together??

[dhaugland]

Hi Ajax - I believe I am making progress and learning every day, but I am running out of time in getting this project complete. Wondering if you could help me with a couple of challenges.

CP Log Rev H.zip

1. My interchangeability table (and sub-form) seems to be working pretty much like I want where I can now select the options associated with each CP. However, it seems that when I enter a new CP and select the options, they don't want to "stick" at first. If I move to a previous CP and then back again, the options will change. Once I fix them, they seem to stay, but I need them to stick the first time. Any idea why this happens and how I Can solve?
2. It may seem trivial, but I would like the interchangeability sub-form to look like the "drawings impacted" sub-form. By this, I mean, I don't want to see the botom row where it shows # of records or the search stuff. I will always have 3 per CP so don't want to use up the realistate on the form.