Form field hiding in masters (1 Viewer)

[Ravi Kumar]

Dear all,
I have master table field where I have 21 coloumns
1 material number
2 gauge type(type name in the coloumn heading and reading will be entered in the fields)
In the gauge type I have total 20 types .but each material number should only be checked using the guages specified in the drawing.
Now in the form I have put all the 20 types to enter,but now the user finding it difficult to enter finding specific gauge reading in the specified gauge type, as all the gauge types are present in the form.
So I made a master table where I have entered material type and specified gauge type to be used.
Now in the form when the user selects a material.only the types I have specified in the master table should be visible and all others to be hidden.
Please tell (explain) me how to achieve this.

 

[theDBguy]

Hi. Sounds like you may have a table structure issue. Rather than use each gauge type as a field name (if that's what you're doing), it should be stored as a record instead (rows). It may or may not apply, but you might want to look up about the entity-attribute-value (EAV) model.

 

[Ravi Kumar]

Hi. Sounds like you may have a table structure issue. Rather than use each gauge type as a field name (if that's what you're doing), it should be stored as a record instead (rows). It may or may not apply, but you might want to look up about the entity-attribute-value (EAV) model.

Thank you for your reply...and the table structure is not the problem...I am outside now..once I am home I'll attach the DB...I think that will help me to get your help.

 

[Ravi Kumar]

Sorry for my late reply...
As the DB contained confidential info , I made a crude db containing the same pattern. Could you please look into it , & please explain me how could my request can be accomplished.

 

[jdraw]

I think you should try again to explain what you are trying to accomplish.
Always better to have 2 sets of data -- the before and the after/expected output from some process/activity. A table with all 0 values and some cryptic "codification" does not supply enough context to communicate your requirement (to me at least).
Readers should NOT have to guess the meaning of your request.

 

[Gasman]

Thank you for your reply...and the table structure is not the problem...I am outside now..once I am home I'll attach the DB...I think that will help me to get your help.

I would beg to differ, that is exactly your problem. You are thinking in an Excel way not a database way.

 

[Ravi Kumar]

I would beg to differ, that is exactly your problem. You are thinking in an Excel way not a database way.

Sorry , I was bit busy in meeting preparations hence I couldn't see your reply.
I studied(only concept) the EAV database structure and as you suggested it will store only non-zero items hence makes the databse compact & structured.
I will study bit more today & I will work on this.(kindly provide me a link if you can)
And also yes , I was using excel for long time , hence I would be messing the key points of database structure

But my issue is this:
1. In my master table I have defined which types to be checked for which material number.
2.In my form I have given fields for all the types , but if a user types a material number , after update it should show only the fields relevant ot the material type as defined in master table.(all the other fields should not be visible)

That is the user should not be able to enter the data in wrong fields.
And also I think it will be error proof as the user can't enter in the irrelevant fields.

How to accomplish this ??
please ask me if anything I have missed to summarize.

 

[Gasman]

Well it now depends on how you want the interface to look.?
One way would be to, as you add a new material number record that requires these material types, you query your master table for all the records for that material number and append to the linked table, then make sure all are completed with data.? A subform would work for this. This also will only work if these types must all be completed every time. If one material can have four and another five types, then that approach would not work.?

Then you could let the user add them manually, but again only allow them to select relevant types for that material and ensure they do not duplicate the types.? Or pehaps add them all and then allow the user to delete non required types.?

HTH

 

[Ravi Kumar]

Thank you for your reply ,
In this case I can't give the user this freedom to select the relevant types.
As he could miss one type of gauge & it may result in rejection.
Hence the user interface must look like this:

The form has a combo box of material number which has the source at matser table.
When the user selects that particular material number , only the gauge types which are relevant to that material number(already entered in masters).should be visible.
if you could check my attached db you'll get a better idea.
or you can modify that db in the way you are explaining in the above response so that I can learn hat.

 

[Gasman]

Well I am on holiday in the USA at present, so that is not going to happen.

Perhaps someone can chip in with some other suggestions.?, though creating the blank records ready, could be a way to go, as they need to be completed anyway from what you have said.? Only if the user decides to cancel that operation, would those records be an issue, and you could write them to a temp table first perhaps.

 

[Ravi Kumar]

Well I am on holiday in the USA at present, so that is not going to happen.

Perhaps someone can chip in with some other suggestions.?, though creating the blank records ready, could be a way to go, as they need to be completed anyway from what you have said.? Only if the user decides to cancel that operation, would those records be an issue, and you could write them to a temp table first perhaps.

Okay have a safe trip

 

[Pat Hartman]

It is not always easy to transition from a spreadsheet to a relational database. I did this for you but you will need to do some studying to understand what I did and why. You needed three tables - material, gauges, and gauges attached to a material. Then you needed a form with a subform to create the schema. I didn't build the form to manage the GaugeTypes but you need one.

Look at the relationship diagram to see how the three tables are connected. Open the tables in design view to see the unique indexes that I created to prevent bad data.

The only code I wrote was to generate a sequence number which I used to make the crosstab reproduce your master table. There are other things that need to be done if you want to keep the concept of the sequence number which will allow you to change the number and resequence the guages. I didn't bother since I have no idea if this is what you want so post back after you have had a look and I can give you more ideas.

 

[Ravi Kumar]

It is not always easy to transition from a spreadsheet to a relational database. I did this for you but you will need to do some studying to understand what I did and why. You needed three tables - material, gauges, and gauges attached to a material. Then you needed a form with a subform to create the schema. I didn't build the form to manage the GaugeTypes but you need one.

Look at the relationship diagram to see how the three tables are connected. Open the tables in design view to see the unique indexes that I created to prevent bad data.

The only code I wrote was to generate a sequence number which I used to make the crosstab reproduce your master table. There are other things that need to be done if you want to keep the concept of the sequence number which will allow you to change the number and resequence the guages. I didn't bother since I have no idea if this is what you want so post back after you have had a look and I can give you more ideas.

Thank you so much , will look after it.