subform unbound

Question

I have a unbound form and unbound subform based on the same query.  the subform is automatically linking to the master and only displaying the record from the master.  I want the subform to show all the records in the query.  I made sure the subform is not linked in the properties but for some reason the form and subform are linking.  how do I fix this?

Answer 1

I need two views on one page. 

One view binds the main form's textboxes to a query so that new records can be added by clicking on a listbox that fills the textboxes with data, and with the ability to scroll back through records. 

Other view is a datasheet, of the same query and data, to be used to monitor entries as records are added, with the ability to add/update/delete a record in the datasheet.  This would show all records from the query.

as I scroll through each main form's records, the sub-form datasheet displays the record being displayed in the main form - just like they are linked, but I don't want them linked.

please let me know if I am over doing this and there is a simpler way

Answer 2

Do you mean VBA, VB is a different animal. 

Have you ever looked at a Split form? 

Why are you going through this convoluted methodology? it seems this could be done a LOT simpler. Maybe if you explained your workflow we could make suggestions.

Answer 3

I can not help as I do not know VB.

Why not use a query for both and then you could have main form laid out to show contents of a single record and subform in datasheet view to show multiple records.

Answer 4

1- ok, I do bind it in vb (see below)

2- no, I do not want them linked...I want them independent of each other

3- I want to display all the records from the query - independent of the main form.  the main form is used to work on one record at a time and the subform is primarily used for display- to track the records being added/updated

4- yes.  can I have a form to display records from a query, one at a time and a subform using the same query, but for a different/independent reason.

certain record sources are used based on the value of an option group and combo box.  once the combo box is updated, the textboxes on the form are loaded with data from the record sources.  the subform record is similar, but I have it set up as a datasheet to display several of the records.

Here is the vb code - not sure how to paste it using the same vb format in Access.

Private Sub Combo76_AfterUpdate()

Dim dbs As DAO.Database

Dim rs As DAO.Recordset

Set dbs = CurrentDb()

'here are the Master Form textboxes

    Me.ReferenceNumber.ControlSource = ""

    Me.txtID.ControlSource = ""

    Me.txtDate.ControlSource = ""

    Me.EVEntry.ControlSource = ""

    Me.VendorID.ControlSource = ""

    Me.AccountNumber.ControlSource = ""

    Me.ItemAmount.ControlSource = ""

    Me.ReferenceAmount.ControlSource = ""

    Me.Payee.ControlSource = ""

'check the option in the Option Group

If Me.Frame80.Value = 1 Then

    'check the value in the ComboBox76

    If Combo76.Text = "jgas-bsb" Then

        'set the record source

        Me.RecordSource = "Bank-JGAS-bsb Query"

        'put the value of the ID field to this textbox on the form

        Me.txtID.ControlSource = "ID"

        'put the value of the Check Number to this textbox on the form

        Me.ReferenceNumber.ControlSource = "Check Number"

        'put the value of the Amount Debit to this textbox on the form

        Me.ItemAmount.ControlSource = "Amount Debit"

        'set the record source

        Me.Child78.SourceObject = "Query.JGAS_Transactions_Query"

    ElseIf Combo76.Text = "jgas-cit" Then

        Me.RecordSource = "Bank-JGAS-cit Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Serial Number"

        Me.ItemAmount.ControlSource = "Amount"

        Me.Child78.SourceObject = "Query.JGAS_Transactions_Query"

    ElseIf Combo76.Text = "jgas-mnb" Then

        Me.RecordSource = "Bank-JGAS-mnb Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Check Number"

        Me.ItemAmount.ControlSource = "Debit Amount"

        Me.Child78.SourceObject = "Query.JGAS_Transactions_Query"

    ElseIf Combo76.Text = "jgas-mnb-pr" Then

        Me.RecordSource = "Bank-JGAS-mnb-pr Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Check Number"

        Me.ItemAmount.ControlSource = "Debit Amount"

        Me.Child78.SourceObject = "Query.JGAS_Transactions_Query"

    ElseIf Combo76.Text = "can-bsb" Then

        Me.RecordSource = "Bank-Can-bsb Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Check Number"

        Me.ItemAmount.ControlSource = "Amount Debit"

        Me.Child78.SourceObject = "Query.Checks-Cannon"

    ElseIf Combo76.Text = "can-cit" Then

        Me.RecordSource = "Bank-Can-cit Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Serial Number"

        Me.ItemAmount.ControlSource = "Amount"

        Me.Child78.SourceObject = "Query.Checks-Cannon"

    ElseIf Combo76.Text = "can-mnb-pr" Then

        Me.RecordSource = "Bank-Can-mnb-pr Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "Check Number"

        Me.ItemAmount.ControlSource = "Debit Amount"

        Me.Child78.SourceObject = "Query.Checks-Cannon"

    End If

'do this if the option group value is this

ElseIf Me.Frame80.Value = 2 Then

    If Combo76.Text = "jgas-bsb" Or Combo76.Text = "jgas-cit" Or Combo76.Text = "jgas-mnb" Or Combo76.Text = "jgas-mnb-pr" Then

        Me.RecordSource = "JGAS_Transactions_Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "ReferenceNumber"

        Me.ItemAmount.ControlSource = "ItemAmount"

        Me.txtDate.ControlSource = "TranDate"

        Me.AccountNumber.ControlSource = "AccountNumber"

        Me.VendorID.ControlSource = "VendorID"

        Me.Payee.ControlSource = "Payee"

        Me.Child78.SourceObject = "Query.JGAS_Transactions_Query"

    Else

        Me.RecordSource = "Cannon_Transactions_Query"

        Me.txtID.ControlSource = "ID"

        Me.ReferenceNumber.ControlSource = "ReferenceNumber"

        Me.ItemAmount.ControlSource = "ItemAmount"

        Me.txtDate.ControlSource = "Date"

        Me.AccountNumber.ControlSource = "AccountNumber"

        Me.VendorID.ControlSource = "VendorID"

        Me.Payee.ControlSource = "Payee"

        Me.Child78.SourceObject = "Query.Checks-Cannon"

    End If

End If

dbs.Close

Set rs = Nothing

End Sub

Answer 5

1- I have a unbound form and unbound subform based on the same query. 

2- the subform is automatically linking to the master and only displaying the record from the master. 

3- I want the subform to show all the records in the query. 

4- I made sure the subform is not linked in the properties but for some reason the form and subform are linking.  how do I fix this?

1- Your statement isn't valid as an 'unbound form' would not have any records!  Below (3) you say 'to show all the records in the query.'  So the form must be bound to the query.

2- Do you mean that you have the Master/Child links of the main/subform set?  

3- Normally a subform is used to show related information from another table such as all the available colors for an item. 

4- Are you using the same query for both main and subform?

Post the SQL of your query(s).