help!!

[tselie115]

hello guys hope everybody is fine.
i have attached 2 jpgs for my access database.
in "Untitled 1" u can see and their subtypes
in "Untitled" u can see the combobox types and the subsubform holding the material column, Size1 columnn. Dim1 Column...
how can i autofill records in the subsubform when i select the value from the combobox types.
the records should be filled by the subtypes from the attachement "untitled1"
Thank you

 

[MagicMan]

Many ways to do this.
1st...to reference a selection in the combo box...
Me.Combobox.Column n starts at 0

2nd...To feed bound fields in the subform from a combo box selection...in the After Update of the combo box set the recordsource for the subform to the combo box selection then requery

Or

1) Populate a text field that could by Visible=false on the main form with the combo box selection.
2) When adding the subform, link it's recordsource to that text box.

Or....many more ways depending on your specific requirement.
Hope one of these examples helps.
Smiles
Bob

 

[tselie115]

Thx BoB
the problem in that the number of records is unpredictible.
it can be 1, 10 or 100.
i think feeding bound fields is not practical in here!
What im trying to find out is how to create and copy the records from the "Untitled 1" form to the subform in "untitled" based on the selection in the combobox at the afterupdate event.

 

[MagicMan]

I really do not understand your question.
In any event, using VB code in the combobox after update, you can do whatever you want with the table data and subform data. You may wish to update the tables and do a requery, which will populate the subform, if it is a bound subform. If the subform is not bound, then the data collection within the subform is populated probably in the On Load or On Current events off the form. That code can be made into a function, and the function can be called in the after update of the combobox, once the tables have been updated. As I identified, the columns of the combo box can be referenced with the Me.combobox.column(0) where this is the first column, Me.combobox.column(1) is the second, etc.
If I have totally misunderstood your need, please correct me or clarify your need.(The form is complex and probably has some nice code behind it, and the question seems too simple.)

 

[tselie115]

ok thx i ll try to be clear.
what i have is a setup form and its subform to setup up types and their own subtypes.(each type has different subtypes)(attachement:untitled1)
the user will have to enter the types and appropriate subtypes once.

i also have a main form and subform and subsubform.
in the main form there s a types combobox
in the subsubform there s a subtypes field
i want that each time the user choses a type from the combobox in main form, the appropriate subtypes(from the setup form) records will be automatically filled(created and filled) in the subsubform and saved to the corresponding table.(attachment:untitled)

thank you!

 

[tselie115]

Well thank you for your help guys with no replies.

after some researches, i was able to change the below code to copy the records on the afterupdate to my form.
heres the code:

Private Sub type_AfterUpdate()
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from Materialtable1 where ID =" & Me.Type.Column(1))
With tb
Form_InventoryTransactions.material = tb!Material1
Form_InventoryTransactions.Rate = tb!Rate1
Form_InventoryTransactions.Unit = tb!Unit1
End With

but its only creating and copying the first record! i need to copy the full recordset so whats wrong?

 

[MagicMan]

Sorry I haven't been on the forums for a bit, busy hanging off a 3 story high ladder painting the outside of the house.

If I understand correctly (usually I do not know enough), you have more than one record you want copied to the subform recordset from Materialtable1. Personally, I would use VB code to update the tables and then requery to refresh the form. You may want to try looping through the recordset while advancing the subform using the DoCmd.GoToRecord , , acNewRec.

Private Sub type_AfterUpdate()
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from Materialtable1 where ID =" & Me.Type.Column(1))
Do until tb.EOF = True
    With tb
         Form_InventoryTransactions.material = tb!Material1
         Form_InventoryTransactions.Rate = tb!Rate1
         Form_InventoryTransactions.Unit = tb!Unit1
    End With
    tb.MoveNext
    DoCmd.GoToRecord , , acNewRec
Loop
tb.close
db.close

Not sure if that will work....but it might lead you in the right direction. Like I said I use VB code to update the tables, then requery to refresh.
Smiles
Bob

 

[tselie115]

in fact, you are right its not working!
its executing then the last record appears for seconds, the combobox and the records will be blank after dat!
What is really happening is that the combobox is on a mainform and the records newly created should be on a subform!
the code "DoCmd.GoToRecord , , acNewRec" is creating a new record on the mainform instead of the subform

how would u update your tables and then requery in VB?

 

[MagicMan]

Since I do not know you table or field names, I will just give you a design write up:

1) Using the same loop to read the data from Materialtable1 remove the DoCmd
2) Use the Insert Into (Check Access Help)
EG: db.Execute " INSERT INTO Employees " _
& "(FirstName,LastName, Title) VALUES " _
& "('Harry', 'Washington', 'Trainee');"
3) After the loop is completed insert a Me.Refresh or Me.Requery

Smiles
Bob

 

[tselie115]

not working, it is obvious that i have a problem with ma code:

Private Sub type_AfterUpdate()
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from Materialtable1 where ID =" & Me.Type.Column(1))
Do Until tb.EOF = True
With tb
db.Execute " INSERT INTO Inventory Transactions " _
& "(material, rate, unit) VALUES " _
& "(tb!material1, tb!rate, tb!unit);"
End With
tb.MoveNext

Loop
Me.Refresh
tb.Close
db.Close

 

[MagicMan]

Place a stop at the Private Sub type_AfterUpdate() and single step (PF8). Im using Access 2007, so all I have to do is place the cursor over the field in the line after it has executed to see the value. You can also use Debug to show values in the immediate window.
Hope that helps.

 

[tselie115]

thanks for your help really appreciated!
how to replace DoCmd.GoToRecord , , acNewRec to create a new record in the subform(inventory transactions) insted of the main form because this code is creating a new record in the mainform not the subform

 

[boblarson]

thanks for your help really appreciated!
how to replace DoCmd.GoToRecord , , acNewRec to create a new record in the subform(inventory transactions) insted of the main form because this code is creating a new record in the mainform not the subform

If the code is in the main form but you want a new record on the subform then use:

Me.YourSubformContainerName.Form.Recordset.AddNew

(making sure to use the name of the subform container control - the control that houses the subform on the main form and not the subform name itself, unless it is the same name as the subform container control)

Also make sure to leave the .Form. part exactly as shown as that tells Access you want a property of the subform and not the subform container.

 

[tselie115]

yumiiiiii its working thanks guys for ur valuable help!
now i have to fill the sub subform thanks for your help again

 

[tselie115]

one more thing is that when i m choosin one type from combobx and then rechose another type, the records are adding!
what i really want is to erase the old records and replace them with the updated ones!