Autofill Field from Combo Box not working

[tammyl]

Hi,

I have a subform built into a tab control in my access database.
This form has a combination of unbound text fields and fields linked to a table.

so i don't duplicate data in my second table, i am using a combobox to display data on the form. the list has Invoice Id, Vendor & Invoice Number. Field name is cboParentInvId.
row source for this field is select tblInvoices.InvID, tblInvoices.Vendor, tblInvoices.InvoiceNo from tblInvoices
Bound Column = 1
Column count = 3
Column widths = 0,5cm,3cm

In the AfterUpdate event Procedure i have this code
Private Sub cboParentInvID_AfterUpdate()
txtParentInvNo = Me.cboParentInvID.Column(2)
Me.Requery
End Sub

the text fields i wish to populate are
txtParentInvNo - Unbound

When i select the record from the combo box, the Vendor code is entered into the field but the ParentInvNo field is blank.

What am i doing wrong? How do i autofill text fields from the combo box so I don't have to duplicate data.

Any help appreciated.

Thanks
Tammy

[CreganTur]

If the textbox object you want to work with is on a tab control, then you're going to have to reference that control and then the textbox, because that's where it is. Example:
me.TabControl1.Textbox

[tammyl]

Hi CreganTur,

Thanks for your reply. I know this should be easy but I just can't seem to get it to work.

Tab Page Name = SubVendor Invoices (This is the name shown in the tab)
Subform Name under Tab = SubInvoices
Subform Source Object = fsubInvSup

These are the vba i've tried and the text box does not get updated.

Private Sub cboParentInvID_AfterUpdate()
'Me.SubInvoices.SetFocus
'Me.SubInvoices.txtParentInvNo = Me.SubInvoices.cboParentInvID.Column(2)
'Me.[SubVendor Invoices].txtParentInvNo = Me.[SubVendor Invoices].cboParentInvID.Column(2)
'Me.txtParentInvNo = Me.cboParentInvID.Column(2)
'Me.Form.fsubInvSup.txtParentInvNo = Me.Form.fsubInvSup.cboParentInvID.Column(2)
Me.Form.SubInvoices.txtParentInvNo = Me.Form.SubInvoices.cboParentInvID.Column(2)
End Sub

I'm at a loss. I'm doing something wrong.

Cheers tammyl

[CreganTur]

Can you post an example database that shows what you're trying to do?

[OBP]

I think the problem may be the
me.requery
which is probably resetting the Unbound field back to Null.

[tammyl]

Hi CreganTur,

Sample attached.

Much appreciated.

Tammy

[CreganTur]

Sorry, I should have specified that it needs to be in 2003 format- I don't have 2007.

[tammyl]

Hi,

2003 format attached.

Cheers

[OBP]

This works fine for me

Me.txtParentInvNo = Me.cboParentInvID.Column(2)

[tammyl]

Hi,

I have now got this to work, so thank you for your time.

Issue i have now is that when the ParentInvID field is populated for the current record, the ParentInvId field for all records including a new record, is populated with the same information.

Also, when i close & reopen the database or switch between design & form view mode, i lose the data.

This field is currently an unbound field as i didn't want to store it in two tables.

Hope you are happy to help.

Thanks
Tammy

[tammyl]

Updated version attached.

[OBP]

tammyl, yes I am afraid that is how forms in Datasheet view and Continuous mode work with unbound fields.
I have some concerns about the design of your Mainform/Subform setup, I quite like the Filtering setup but why are there no Bound Mainform fields?
If they were "Bound to the Project Table you could link the Invoices Subform via the ProjectID, which means you would not have to select the ProjectCode.

[tammyl]

Hi OBP,

Sorry don't quite understand what you are saying. When selecting a project code in the filtering options on the main form, only those matching records are displayed in the invoices subform. The project code is a selectable combo box for when I am entering new invoices which I usually only have the Vendor filter on from the mainform, therefore need the option of project code to be selectable.

I'm unsure how to setup what you've suggested, especially if the Project code is blank in the filter selection.

I still have not been able to solve how i get the Parent Invoice ID & Parent Contract No. onto the SubVendor Invoices subform without duplicating the data into the tblSubInvoices table.

Hope someone can help.

Thanks
Tammy

[OBP]

You can't get the data in to the form with unbound fields.
You could try including those fields from the Project table in the Query that supplies the SubForm with it's data. Providing that the Recordset is updateable it would be OK.
If you had the Project ID on the Mainform you would not have to select the project in the subform. How do you enter a new Project?