Buttons to increment by one + split screen records

[Berkeleygerrell]

Hello All

I am brand new to the forum and hoping one of you can help.

I am currently in the process of creating a rudimentary NCR database and I am struggling to do a couple of things, hopefully you can shed some light.

I have attached the database for you guys to have a look at.

there are 3 key problems, all of which apply to the form titled Current run

Firstly, When i open the form current run I would like this form to create 2 independent records on the table titled CT NCR database auto populating the work center with index 1 and index 2, while maintaining the same date and shift

Second problem: I would like to replace all the text box titles with buttons that upon clicking add one to the value next to them, (Ideally i would also like to lock these text boxes so that they are not editable from this form?

Third and final problem you will see at the bottom of this form there are drop down boxes I would like to be able to select a value from the table CT NCR reason and have these values save into the column CT problem 1 / 2 on the CT NCR database table.

Hopefully someone can help as i am having no joy on the internet

Kind regards

Berkeley

 

[CJ_London]

First - you cannot display two records on the same form as you are attempting to do - you will need to use two subforms. However I see no way of identifying 'index1' and 'index2'
second - dissociate the label with the control by deleting the label. You can then either paste the label back again as a dissociated label or add a button - both have the same events. You can lock the control so users cannot manually change it - but what happens if they click more than the should have done?
third - set the rowsource to the combo

other comments -

you have spaces in field and table names - not a good idea, but if you need to replace than with underscores
you are using lookups in your tables - again, not a good idea, they just confuse what is really there

 

[Berkeleygerrell]

First - you cannot display two records on the same form as you are attempting to do - you will need to use two subforms. However I see no way of identifying 'index1' and 'index2'
second - dissociate the label with the control by deleting the label. You can then either paste the label back again as a dissociated label or add a button - both have the same events. You can lock the control so users cannot manually change it - but what happens if they click more than the should have done?
third - set the rowsource to the combo

other comments -

you have spaces in field and table names - not a good idea, but if you need to replace than with underscores
you are using lookups in your tables - again, not a good idea, they just confuse what is really there

Hi CJ_London

Thank you for this, I have now changed my attack.

one question for you is there a method of having a button that when opening a form it will automatic create a record with that date, and then if you were to reopen that form it would search to see if a record existed with that date and subsequently open the record instead of creating a new one

Kind regards

Berkeley

 

[CJ_London]

Not sure of your process for getting the date - but assuming that is a control (I'll call it txtDate) on the form that the button is on the code in the button click event would be something like

docmd.OpenForm,,,"myDate=#" & format(txtDate,"mm/dd/yyyy") & "#"

if the record exists, it will go to that record, otherwise it goes to a new record

you might want to prepopulate the date field with the searched for date if not found in which case the code would be

docmd.OpenForm,,,"myDate=#" & format(txtDate,"mm/dd/yyyy") & "#",,format(txtDate,"mm/dd/yyyy")

And in the forms current event you would put

if acNewRec then myDate=cdate(openargs)

 

[Berkeleygerrell]

Not sure of your process for getting the date - but assuming that is a control (I'll call it txtDate) on the form that the button is on the code in the button click event would be something like

docmd.OpenForm,,,"myDate=#" & format(txtDate,"mm/dd/yyyy") & "#"

if the record exists, it will go to that record, otherwise it goes to a new record

you might want to prepopulate the date field with the searched for date if not found in which case the code would be

docmd.OpenForm,,,"myDate=#" & format(txtDate,"mm/dd/yyyy") & "#",,format(txtDate,"mm/dd/yyyy")

And in the forms current event you would put

if acNewRec then myDate=cdate(openargs)

Thanks for all your help so far CJ_london,

I wonder if you can help with part 2 of this.

I managed to get this working via means of macro, however i now how the longest table in the world,

I am hoping to be dramatically reduce the number of columns that i have now by making forms automatically assign a value to a column upon opening, (the column would be for (AM/PM/Unmanned AM/Unmanned PM/Inspection) reducing the amount of columns i have by 5 and making the data one hell of a lot easier to analyse. would this be possible without overwriting table entries with the same date?

 

[CJ_London]

if that is your current table design, it is constructed incorrectly. I don't understand all the data but as you say you could reduce the number of columns to 25.

There are other 'saving's. You don't need the part description column - that should be fetched fro a parts table in a query when required.

You also have repeating columns for scrap and rework. So suggest your tables and fields might look more like


tblParts
PartPK
PartNumber
PartDescription

tblShifts
ShiftPK
ShiftName

tblWork
WorkPK
RaisedDate
ShiftFK - link to tblShifts PK field
PartFK - link to tblParts PK field

tblQtyTypes
QtyTypePK
QtyTypeName - (batch quantity/front bore oversize/thru bore oversize etc)

tblQtySubTypes
QtyTypePK
QtySubTypeName (scrap/rework)

tblDetail
DetailPK
WorkFK - link to tblWork PK field
QtyTypeFK - link to tblQtyTypes PK field
QtySybTypeFK -link to tblQtySubTypes PK field
Qty

Your current design is based on excel thinking, databases work on opposite principles. For example if you decided you needed another type or subtype in excel you would need to add columns (and to your table in it's current design), also modify calculations etc. With the structure I'm suggesting, you would not need to change anything

 

[Berkeleygerrell]

Hi CJ,

Unfortunately i dont think i will be able to run it quite like that as i am trying to create a live running screen where i can click on the blue box for scrap and rework and it increments by one, please see attached screenshot , unfortunately the access file is too big to attach.

 

[CJ_London]

it wouldn't be a problem, you would use a number of subforms. Don't confuse presentation with data storage. Doing analysis with non normalised data can be difficult/slow/requires a lot of maintenance.

you might add a second qty field to tblDetail and do away with the subtypes.

But it's up to you - it's your app.

 

[Berkeleygerrell]

it wouldn't be a problem, you would use a number of subforms. Don't confuse presentation with data storage. Doing analysis with non normalised data can be difficult/slow/requires a lot of maintenance.

you might add a second qty field to tblDetail and do away with the subtypes.

But it's up to you - it's your app.

Hi CJ,

Thanks for your help so far,

I have manage to delete most of the forms to bring the file size right down so i have been able to attach now,

Could you quickly explain the layout of the sub forms that i would have to use ?

 

[CJ_London]

couldn't get anything to work on your db, so I created an example to demonstrate how it could work. Note I do not understand how you navigate between shifts and part numbers and create new records so this part is not working it just updates the existing record.

the form looks like this - the db will open to it, the click will add one. You may be able to adapt it to your requirements or you may not. It merely demonstrates the use of subforms and the type of table structure I was suggesting

Edit: to demonstrate ease of maintenance, open the qryTypes table and and a new type - when you refresh the form (close and reopen or go to a new record) that newtype will appear. The other thing to note is that if you open tblDetails you will only see records where there is a scrap quantity and/or a rework quantity. (although there are a few test records I forgot to delete before uploading).

This is just the way I've done it because as I said at the beginning of this post, I don't understand your navigation. To create a new 'works' record click on the new record option on the bottom bar and enter date/shift etc, then tick away in the subform.

 

[Berkeleygerrell]

couldn't get anything to work on your db, so I created an example to demonstrate how it could work. Note I do not understand how you navigate between shifts and part numbers and create new records so this part is not working it just updates the existing record.

the form looks like this - the db will open to it, the click will add one. You may be able to adapt it to your requirements or you may not. It merely demonstrates the use of subforms and the type of table structure I was suggesting

View attachment 82118
Edit: to demonstrate ease of maintenance, open the qryTypes table and and a new type - when you refresh the form (close and reopen or go to a new record) that newtype will appear. The other thing to note is that if you open tblDetails you will only see records where there is a scrap quantity and/or a rework quantity. (although there are a few test records I forgot to delete before uploading).

This is just the way I've done it because as I said at the beginning of this post, I don't understand your navigation. To create a new 'works' record click on the new record option on the bottom bar and enter date/shift etc, then tick away in the subform.

Thank you CJ really appreciate this,

A couple more questions,

firstly in this lay out how would i split the shifts by machine? (index 1 and index 2) so i can show both of these at the same time ?

secondly the actual list of failure modes is considerable longer, please see attached. Is there a way to show the key 6 like you have listed and then the remaining failure modes on a dropdown were i can select as necessary ?

Kind regards

Berkeley

 

[Berkeleygerrell]

Thank you CJ really appreciate this,

A couple more questions,

firstly in this lay out how would i split the shifts by machine? (index 1 and index 2) so i can show both of these at the same time ?

secondly the actual list of failure modes is considerable longer, please see attached. Is there a way to show the key 6 like you have listed and then the remaining failure modes on a dropdown were i can select as necessary ?

Kind regards

Berkeley

Also thirdly can i use the reasons / shifts / part number as the PK?

 

[CJ_London]

Also thirdly can i use the reasons / shifts / part number as the PK?

Sort of question usually asked by excel users who can't get away from using tables and queries as if they a spreadsheet - users should not be looking at tables or queries. So not recommended, but you could do but it is inefficient.

Reason is text takes up considerably more space than a number (numbers take 4 bytes, text takes 2 bytes per character plus 2 bytes so an 8 character part number would take 18 bytes) as a text PK that may not be so bad - 18 bytes for the record plus 18 bytes for the index, but as the multiple foreign key for say 10 records plus the primary record, that would be 396 bytes - compared with 88 bytes - so you db starts to bloat. Plus, because of the way indexing works, the larger the index value, the slower it works. If you have a limited range - a few hundred parts it won't be noticeable. I would use it for say lists of US states for example.

Also any corrections to the text requires additional work to correct everywhere it appears.

 

[CJ_London]

firstly in this lay out how would i split the shifts by machine? (index 1 and index 2)

you would need an additional table for machines, and would add a machineFK to the works table. There are a number of subsequent options, the easiest would be to modify the query to filter for one or the other. Create a copy to filter for the other machine, and a new subform to work off the new query.

secondly the actual list of failure modes is considerable longer, please see attached. Is there a way to show the key 6 like you have listed and then the remaining failure modes on a dropdown were i can select as necessary ?

just make the subform scrollable with the most popular sorted to the top (I provided a sortorder column). If you want to select from a list, it will probably require an additional subform

 

[Berkeleygerrell]

you would need an additional table for machines, and would add a machineFK to the works table. There are a number of subsequent options, the easiest would be to modify the query to filter for one or the other. Create a copy to filter for the other machine, and a new subform to work off the new query.

just make the subform scrollable with the most popular sorted to the top (I provided a sortorder column). If you want to select from a list, it will probably require an additional subform

Thanks CJ, im almost there, one final question (i hope).

If i want the buttons on my main menu to open the particular form would this be correct as i am getting a peramiter issue

 

[CJ_London]

i don't use macros so regret I can't help - particularly not knowing the actual error 'parameter issue' could mean anything