Creating a form that resembles a excel worksheet. Pictures included.

[hcuk]

Hello fellow Access users,



I am trying to "Convert" the excel worksheet to a form in Microsoft Access. I'm not quite sure what the best approach is. I've included the screenshot of the excel and a screenshot of the Form that i've created. The form looks good, but the way it works is not what I am going for, it's not inserting into the table correctly. Please Advise and Thanks in advance,






Richard

[ranman256]

I think you may want a form/subform.
The master form has the date? (or whatever is the master id)
and the sub-form has all related records below.

[hcuk]

I think you may want a form/subform.
The master form has the date? (or whatever is the master id)
and the sub-form has all related records below.

Thanks for the reply,

I've tried it with a sub form but when I finish entering everything at the top section, and tab to the sub form, everything in the top section is cleared. I want the top section to remain there when I enter data into the sub form.

It also isn't inserting into the table correctly, I've included a screen shot of what it's supposed to look like.

[ranman256]

The form /subform WILL enter it correctly, so I dont think you have that goin on.
You may have a split form.
Use the wizard to build a master/child sub form, and make sure you tell it the KEY values.

[hcuk]

The form /subform WILL enter it correctly, so I dont think you have that goin on.
You may have a split form.
Use the wizard to build a master/child sub form, and make sure you tell it the KEY values.

Would I need to create a new table that contains only columns from the bottom section, and remove those columns from the master table.

[June7]

Probably Yes.

Sounds like you need a better understanding of relational database concepts. Maybe this will help http://www.rogersaccesslibrary.com/

[hcuk]

Probably Yes.

Sounds like you need a better understanding of relational database concepts. Maybe this will help http://www.rogersaccesslibrary.com/

Thanks. I've figured it out. created a table and created a relationship between two columns.

But i have another question. I'm trying to create a clear button, to clear the master form and the sub form.
Currently this is my code that will clear the fields in the master form, would it be possible to add more code to clear the sub form as well?

Private Sub Clear_Button_Click()    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    ctl.Value = Null
    End If
    Next ctl
End Sub

Please let me know if need to create a new thread for this.

[June7]

Are these bound forms and bound controls? Don't want to 'clear' bound controls - that would delete data.

Maybe you want to move to new record row?

[hcuk]

Are these bound forms and bound controls? Don't want to 'clear' bound controls - that would delete data.

Maybe you want to move to new record row?

They are bound controls. If I went the new record route, I'm not sure how to code it so that it moves to a new record for both the subform and master form.

[June7]

Moving to new record on main form is easy.

DoCmd.GoToRecord , , acNewRec

Subform should not be a concern because there won't be any records since the main form is a new record, assuming the forms are properly linked.

[hcuk]

Moving to new record on main form is easy.

DoCmd.GoToRecord , , acNewRec

Subform should not be a concern because there won't be any records since the main form is a new record, assuming the forms are properly linked.

That worked perfectly. Thanks June7! You've been a great help!