I am writing a simple Access application to produce costings for package tours. I have an unbound sub-form which I intend to use for data entry for each item of the form. I cannot enter the data directly into a sub-form bound to the items table because I have various combo boxes & queries in my data entry form. I therefore need to enter each item, and "submit" the data to the items table item by item. I have a second sub-form that will show show the list of items from that table. What is the code that I need to submit that data to the table?

Is there any way you can bind your main table to the main form, even if you have a few hidden fields on it, so that you can associate the subform to the main form by linking fields? This is the easiest thing to do.

If you are determined to use a sub form, and then another sub form to show the same data, then you are in for a lot of code, data validation, and error checking etc.


mmcdonal

I don't think I can. The main reason being that the table would have to have combo boxes that are linked to each other - for example if I choose "type of supplier" HOTEL from a drop down list, my next drop down list will then be "supplier" and will only list hotels. If I do this in the table I think it changes every item in the table rather than the specific item. Somebody told me that I could only do it using code but I don't really know where to start.

Any other thoughts?

Here is the code I use... give it a try:

Function submit_Close()
On Error GoTo error_submitClose
    Call OpenAConnection
    Call ADDRecord
Exit Function
error_submitClose:
    Dim EMsg, ETitle, ERESP, EStyle
    EMsg = (Err.Description)
    EStyle = vbCritical + vbOKOnly
    ERESP = MsgBox(EMsg, EStyle, ETitle)
    DoCmd.CancelEvent
End Function


Function OpenAConnection()
    Set conn = New ADODB.Connection
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<PATH TO YOUR BACK END DATABASE>;Persist Security Info=False"
    conn.Open sConnString
End Function


Function ADDRecord()
    Set Rs = New ADODB.Recordset
    Rs.ActiveConnection = conn
    Rs.CursorType = adOpenStatic
    Rs.LockType = adLockPessimistic
    Rs.Open "<YOUR TABLE NAME>"
    With Rs
        .AddNew
        !<YOUR TABLE FIELD NAME> = Forms![<YOUR FORM NAME>]![<YOUR CONTROL NAME>]
        .Update
        .MoveLast
    End With
    Rs.Close
    conn.Close
    Set Rs = Nothing
    Set conn = Nothing
End Function

I just load the submit_Close() function into the ON-CLICK event of my submit button on the form. Post back to let me know how this works for you.

Rick

Why don't you just have the unbound subform bound to the table but display it in single form view. This will be the form where the user's enter/edit items. Keep the other subform that displays the list of items that have been entered. Basically, that's what you're doing anyway.

In the "OnCurrent" event of the list form, you could set a hidden text box on the main form to ID of the item selected in the list form. Then you could bind the "edit" subform to that text box. When the user selects a line item, that line item will automatically appear in your edit subform so they can edit it.

The code you'll need in the OnCurrent event is:

Me.Parent.txtboxcontrol = Me.lineitemid

You'll have to set the Master/Child Link fields for the "edit" subform object manually.

QED. And MUCH better than writing code to manually add/update records. Plus it has that nice little added feature that you don't have to write much code to allow the users to edit the line item.

BTW, you are correct about changing the RowSource based on selections in other fields in a record. If you change it for the control, it changes for all records showing. And this means that the other records will only display data if they would use the same RowSource.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org