Can I have a form that will allow me which of 2 tables my inputs are for?Access 2010

[Corwin Moyne]

OK. I have 2 tables with identical fields. One is called 'External Dies' and the other is 'Internal Dies'. Although the fields in the tables are identical, they have different relationships to another form, 'Product Codes'. The Internal has a one to one relationship to its 'Product Code', where as the External has a one to many relationship as it can be used with many Product Codes. Because of this, I assume I need a separate table for each. I would like a form that will give me a combobox that will give me 2 options: internal or external, then the corresponding table will be populated. Is this possible?

Its important to note, that although I am a Java Programming student, I have no VBA experience what so ever.

[Rabbit]

It's certainly doable through code but wouldn't it be easier to just use different forms?

[Corwin Moyne]

Hey Rabbit. I have it as 2 forms now. The reason I am asking is this pattern continues in other areas. Everything is split into 2 camps. Internal and External. If I could work how to do it once, I could then use the same technique in other forms.

[Rabbit]

Well, after rereading the original post, I don't think you need two different tables.

Explain your data structure in more detail and we may find that it's not necessary to have different tables.

[Corwin Moyne]

OK. I have a product code e.g 3115AA. This product code will have only one internal die (a die is used in the production process during printing) e.g DC6013. It will also have an external die e.g DC5092. The external die can be used with many other product codes where as the internal die has only one product code. Both dies 'revolutions' are updated after each use.

[Rabbit]

Can you post some sample data from both internal and external?

[Corwin Moyne]

I don't really know what to post. You can have a look at the relationships here:

http://imageshack.us/photo/my-images/15/capturezst.png/

[Rabbit]

What I want you to post is data. Like this:

Table1
Field1 Field2
abc    1
def    2

[Mihail]

This structure will allow you to change the row source to your form to any table:

Private Sub Combo_Click
    Select Case ComboValue
        Case Value_1
            Me.RowSource = "NameOfTable_1"
        Case Value_2
            Me.RowSource = "NameOfTable_2"
        ..............
        Case Value_n
            Me.RowSource = "NameOfTable_n"
    End Select
End Sub

Note please that you can use this for queries too by replacing NameOfTable_n with NameOfQuery_n
Of course, in order to be able to change data or to add new records the query (queries) must be updatable.

This is pseudo code.
Not tested but should work.

[NeoPa]

While Mihail's post may well answer the original question fairly well and clearly (and may even be the one to select as Best Answer), I would advise that you follow Rabbit's advice and proceed to look at your table structures (I'm fairly certain Internal and External data should be stored in the same table with a simple flag reflecting which type a record represents.) as a solution to your situation.