How to use an unbound combo box to fill fields of a subform

[AlvaroCity]

Hello everyone. First of all I have to say that I am quite amateur with vba.
I am developing my own database and now I have bumped into a problem. I would like to use an unbound combo box to fill the fields of a sub-form using a "add bottom".
The issue is that I don't know where to start, my imagination is quite scarce.

Any help or ideas would be very appreciate.

 

[stopher]

I think you need to explain more about what you want to do. What are the values in the combo? What fields do you want to fill in in your sub form and with what? i.e. where does the data come from that you are putting in the fields.

But in principle you can create an after update event on the combo and in this event you can update fields in the subform.

 

[arnelgp]

while editing the form, select the subform.
create:
Master Link Fields: (combobox name)
Child Link Fields: control in your subform which corresponds to combobox.

when you type in the form for new record, your textbox (correspondss to combo) will autofill. also make the textbox as Locked (readonly).

 

[AlvaroCity]

This is the appearance of my database
(Factura) is Invoice
(Albaran) is Dispatch Note
Fecha is Date
Cliente is Cliente


Combo box: SELECT tblAlbaran.AlbaranID, tblAlbaran.AlbaranNumero, tblAlbaran.ClienteID, tblAlbaran.Fecha, tblAlbaranFactura.FacturaID, CCur(DSum("TotalParcial","qrySubAlbaran","[AlbaranID]=" & [tblAlbaran]![AlbaranID])) AS TotalAlbaran
FROM tblAlbaran LEFT JOIN tblAlbaranFactura ON tblAlbaran.AlbaranID = tblAlbaranFactura.AlbaranID
WHERE (((tblAlbaran.ClienteID)=[Forms]![frmFactura]![cboClienteID]) AND ((tblAlbaranFactura.FacturaID) Is Null))
ORDER BY tblAlbaran.AlbaranNumero;

Column number 6

Column widths 0cm;4cm;0cm;4cm;0cm;4cm

 

[arnelgp]

create a master link field: combo
child link master: albaranid

 

[AlvaroCity]

Thanks Arnelgp for your answer. The only thing I achieved using the child and the master link is just to display data on the subform. But what I was expecting is that I would be able from the combobox is to select the AlbaranID and with a button to add this one to the table "tblAlbaranFactura"
TblAlbaranFactura is a combination of 2 foreign keys: AlbaranID + FacturaID. FacturaID is provided by the MainForm and the AlbaranID should be provided by the combobox. The outcome should be the subform.
I know it is really complicate. I have been working on this for 6 months and now im stuck with this.

 

[arnelgp]

then create a compound Master Link Field and Child Link fields:

AlbaranID;FacturaID

 

[AlvaroCity]

thank you for your answer and for your time again.
I am sorry for my lack of knowledge but I am not too sure how to do that...

 

[arnelgp]

here is a sample i am talking about. on design view, click on the subform and inspect the property->data->master/child link fields.

 

[AlvaroCity]

Hello Arnelgp. Thank you very much for your time again and for your example. I have no words to say how nice you have been and how helpful.
I will try something similar to your example.
I will let you know.
Thank you

 

[AlvaroCity]

Hello Arnelgp. Sorry for replying this post so late. I have been terribly busy. Well I had some time to have a look at it. I've tried your way. I bumped into some problems maybe for my lack of knowledge. The first one was that I am only able to do it as long as the subform only displays exactly the fields from the table "tblAlbaranFactura" which is the combination of 2 tables. If I add some more fields to the query, such as "Invoice Number" or in spanish "Factura Numero", this does not work and It wont link the FacturaId to the Subform for some reason.
The second problem is that, considering that I did not add those extra fields to the subform, I am not able to add the new fields to the table tblAlbaranFactura as I need a new ID field
Thanks again for your help