Simple Many-to-One Problem 1

[joopa]

I know this is simple, but somehow just can't get it staightened out.

I have two tables, one called Work_Order_Request, the other called Type_of_Repair.

The user will first pull up a new Work_Order_Request form and fill in personal information. Then from that form, pull up a Type_of_Repair (sub)form where the problem can be stated. Now if the problem has occured before, the user can select a previous Type_of_Repair, by scrolling back and forth.

My Problem is that I can't seem to bind/link the two Forms together, so this works. They seem completely independent.Most examples I have seen the Main Form is the One and the SubForm is the Many.


The tables are related so Work_Order_Request is Many, Repair is One. Alas One Workorder can only contain One repair, whereas one Type_of_Repair can be on many workorders. The tables are AutoNumbered.

Any help or examples or links appreciated.

J

 

[WillYum]

In the Relationships window, what fields are linked and does it confirm that it is a one-to-many link?

(taking a stab)

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[scriverb]

Are you sure about the restrictions of your tables. What if you one workorder and it is for a washing machine. The work that needs to be done includes, new motor, belts, electrical switch, hoses, and a water pump. This would be a one-to-many relationship with workorder being the one and the needed repairs being the many.

You have stated the opposite above. You wouldn't create a work order for each thing wrong on this washing machine would you?

Before we go any farther you need to think about this. What I have described here is a classic one-to-many relationship between the two tables. You can have many numberous types of repair in a table and select any number of them for one work order.

Post back with your thoughts.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[joopa]

Thanks for your fast reply's.

I double-checked in the Relationships Dialog and the relationships are right and enforced.

Yes, I need one work order for each repair. Different Trades needs to do each repair, sometimes there are no Trades in-house and a new type of repair for an outside company is created.

Maybe I need a One-to-One?

Thanks

 

[scriverb]

I think what you need here isn't a subform but rather a combobox on the main form where you would pick one only repair to be done for this workorder. The list of repairs would be your many table. The combobox would have as its RowSource the table Type_Of_Repair. This could be just the table or a query so that they are sorted alphabetically.

Give this concept a try.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[joopa]

Works Great!
Thanks, just what I needed.

Now I just have to program the Combobox so the user also can enter a new Record. I was going to use a DblClick Event on the Combo Box, opening a new form to enter data for a new record.

Any other Suggestions.

Thanks,

 

[scriverb]

No there is a special Event for just this purpose. Set the Limit To List property to Yes and then there is an Event procedure called NotInList. this is where you put the code to update the table. Go to help for NotInList and take a look at the write up there. Also, the examples. If you are still a little confused post back and I can help write some fairly simple code to do what you want.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Here is some sample code to be used in the On NotInList event procedure:

Dim vResponse As Long
vResponse = MsgBox("This entry does not match the records in the table." & vbCrLf & _
            "Do you wish to Add to the table?", 292)
If vResponse = 6 Then
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("[red][i]yourtable[/i][/red]", dbOpenDynaset)
    rs.AddNew
    rs("[red][i]fieldname[/i][/red]") = Me![[red]Combo0[/red]].Column(1)
    rs.Update
    Response = acDataErrAdded
Else
    MsgBox "Please make another selection"
    Response = acDataErrContinue
End If

This code is setup for a combobox with two columns. Column 1 is the bound column(autonumber) and column 2 is Name. If the user enters a name that is not in the list it prompts the user for direction. Then with a positive response adds the name to the table and also adds it to the combobox list.

If you have to open a form then modify this code by removing the recordset code and including an DoCmd.Openform command using the acDialog parameter to suspend the code until the form is closed.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[joopa]

Bob,
I can't give you enough stars, you are the best. I am sure I will be back soon with another thread. My next project involves Many-to-Many relations.

Thanks again,
Jens