Display Fields based on a Field 1

[aravind3583]

Hi,
I am trying to design a form which takes defects of a particular part and i want to create a form which asks user how many different kinds of parts did he work on and for example if he selects 3 then there should be 3 fields which ask to type the part number and once he finishes typing 3 part numbers then he will be asked how many defects are there on a particular part and if he types 3 then there should be 3 fields displayed on the form..can anyone suggest me how to solve these kind of problems..

thank you,
aravind

 

[VRoscioli]

It might be easier to use a ListBox or somesuch and then parse its contents into a WHERE clause.


-V

 

[aravind3583]

Hi VRoscioli,
thanks for replying to my message..i think you are a little closer to the solution . i did the list box..and i kept the values as 1,2,3 ..10 for the list box..but could you tell me how the parse its contents into a where clause...i can parse its contents but how can i display the fields..the fields wont be showing up in the form.but when the user selects some value then the fields will be showing up...it would be a dynamic form which displays the fields based on the user input..

thanks,
aravind

 

[dhookom]

Can I ask what you table structure might be? It sounds like it might be un-normalized.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aravind3583]

Hi Vroscioli,
sorry for not replying for so many days..i was out of the town...i dont think i normalized my database but what is it got to do with the feature in the form?..i m sorry if i asked you this stupid question but i dont know much of designing database and m jus learning to use the features.....thank you for your patience..

bye,
aravind

 

[dhookom]

If your table structure is wrong then I would not put any effort into creating a form that uses that table structure. Why throw good money after bad?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

I agree with dhookom. Further more, if you create a normalized structure you will probably not need to write code. For what you want to do, it sounds like subforms would suffice.

So for example have tables such as:
[tt]
PartsWorkedOn Defects
WorkedOnID-------| DefectID
PartID |------WorkedOnID
EmployeeID Description
WorkDate
[/tt]
Then you could set up a form where the PartsWorkedOn fields are on the main form, while the Defects are on a subform. Instead of prompting the user "How many defects?", they could just keep adding rows to the subform until they've filled in all defects.

Trust me, using normalized tables will save you a lot of aggravation in the future.

 

[aravind3583]

yes i have normalized my tables...i guess u didnt get my question properly...suppose i have a form which has a field whose label is how many children do u have? and u say 3 ..then it should display 3 text boxes to enter names of 3 children...could u suggest me how to do this...

 

[dhookom]

Are the 3 text boxes bound to fields in a table/query? You can have invisible text boxes:
txt1
txt2
txt3
and a another text box (txtNumDefects) that allows for entry of number of defects. In the after update event of the text box, use code like:

   Dim intI
   If IsNumeric(Me.txtNumDefects) Then
      For intI = 1 to 3
         Me("txt" & intI).Visible = Me.txtNumDefects >= intI
      Next
   End If

Apparently you aren't going to fold to our couriosity regarding your table structure and explain why you would have 3 text boxes become visible rather than adding 3 records to a table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aravind3583]

Thank you dhookom and joe...i have a table something like you created joe..and i have created a subform for the defects..how do i enter multiple records in the subform which is based on defects...like when i inserted a subform based on that table in the main form...i get to fill only one row...but i dont know how to fill multiple records of table in a single form...could you explain that?..thanks for your patience..you guys rock!!!

 

[dhookom]

PMFJI, I would create a table of numbers (tblNumbers) with a single numeric field (Num) and values in records from 1 to 100 (or more). Then use a text box on your form to allow users to enter the number of defects. Add a button to run an append query like:

Dim strSQL as String
strSQL = "INSERT INTO [blue]Defects[/blue] ([blue]WorkedOnID[/blue]) " & _
  "SELECT " & Me.[blue]WorkedOnID[/blue] & " FROM tblNumbers " & _
  "WHERE Num <=" & Me.[blue]txtNumOfDefects[/blue]
DoCmd.RunSQL strSQL
Me.[blue]sfrmDefects[/blue].Requery

This assumes WorkedOnID is numeric and all [blue]blue[/blue] text need to match your actual names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

Let me make sure I understand.
Your main form is based on PartsWorkedOn, and your subform is based on Defects.

First, are the relationships set up correctly? It should be one (ParstWorkedOn) to many (Defects).

Second, on the properties of the subform, have you set the Link Child Fields and Link Master Fields (in my example, they would both be set to WorkedOnID).

If all these are correct you should be able to enter multiple Defect records. What Default View setting do you have for the subform, "Single", "Continuous", or "Datasheet"?

 

[aravind3583]

Hi dhookom,
i tried your solution and i got the result thank you...and thanks joe i did what u said and i made a subform continuous and i got the result...thank you guys...you were really helpful..thank you very much...