delete fields in subform combobox after main form combobox is chan

[deb]

I have a main form with a continuous sub form.

There is a combobox on the main form called "FuelTypeChoice"

The sub form is a continuous form "fPerfEmissionGuaDetails"
There is a combobox on the subform called"FuelChoice"

How can I have all of the records in the subform combobox be deleted if the
main form combobox selection is changed?
so...
as soon as the user changes the FuelTypeChoice all of the FuelChoice are
deleted so the user can make new Fuel Choices.

The ID field between the two tables is PerfEmissionGuaID

Background...
3 tables and a main form with a continuous sub form.

Table one is called tFuel (FuelID, FuelName, FuelType)
example
1, JetA, Liquid
2, Kerosine, Liquid
3, Propane, Gas
4, JetA, Dual
5, Kerosine, Dual
6, Propane, Dual
7, JetA, Multi
8, Kerosine, Multi
9, Propane, Multi

The second table (tPerfEmissionGua) holds the choice of (Gas,Liquid,Dual or
Multi) in the (FuelTypeChoice) field. Can only be one of the choices for the
combox (FuelTypeChoice) that is in the masterform called (fPerfEmissionGua)

The Third table (tPerfEmissionGua) holds the choices of (JetA,
Kerosine,Propane...)
This is used as a combobox (FuelChoice) in a continuous form that is the
subform called (fPerfEmissionGuaDetails)


Please help and thank you,

 

[Jeanette Cunningham]

Deb,
It would help if you explained the business need that this database is
trying to meet.

Jeanette Cunningham

 

[Ken Sheridan]

deb:

One piece of information you haven't supplied is how the parent form
(fPerfEmissionGua) and the subform (fPerfEmissionGuaDetails) are linked, i.e.
what the LinkMasterFields and LinkChildFields properties of the subform
control are. Normally these would be the primary key of the table on which
the parent form is based (tPerfEmissionGua) and the corresponding foreign key
column (field) in the table on which the subform is based
(tPerfEmissionGuaDetails?). So for the moment lets assume both these columns
are called PerfEmissionGuaID and are a number data type.

Now, lets be absolutely sure we are singing from the same hymn sheet before
going further. As I read your post, what you want is to delete all those
rows (records) from tPerfEmissionGuaDetails which relate to the current row
in tPerfEmissionGua as shown in the parent form if the fuel type is changed
from one value to another. If this is the case then you'd need to execute an
SQL statement to delete the rows from tPerfEmissionGuaDetails with the
PerfEmissionGuaID value of that column in the parent form's current record.
You don't really need to worry about whether the value in the combo box is
being changed from an existing value as if it’s the first time a value is
being selected then there won't be any matching rows in tPerfEmissionGua, so
executing the SQL statement won't do anything. Once the SQL statement has
been executed the subform control should be requeried. The code would go in
the AfterUpdate event procedure of the FuelTypeChoice combo box on the parent
from and would be like this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "DELETE * FROM tPerfEmissionGuaDetails " & _
"WHERE PerfEmissionGuaID = " & Me. PerfEmissionGuaID

cmd.CommandText = strSQL
cmd.Execute

Me.fPerfEmissionGuaDetails.Requery

As in your other recent thread fPerfEmissionGuaDetails refers here to the
subform control on the parent form, which might not necessarily have the same
name as its underlying form object.

The above code would be in addition to any code you've already put in the
parent form's combo box's AfterUpdate event procedure to requery the combo
box in the subform so that it lists only fuels applicable to the selected
fuel type.

I hope I've understood correctly what's required here. If not post back
with a more detailed description of what you are looking to do.

Finally, when developing a routine which affects a set of rows like this it
is of course imperative that you back up the data until you are completely
satisfied that its doing exactly what's wanted.

Ken Sheridan
Stafford, England

 

[deb]

Thank you for your help. this worked great for me.
I did put the link ID in the message. however it was burried in all of tons
of wording.
When you guesed the ID, you guesed correctly.


Thank you again!!!

 

[Ken Sheridan]

deb:

So you did! My apologies.

Thank you for your help. this worked great for me.
I did put the link ID in the message. however it was burried in all of tons
of wording.
When you guesed the ID, you guesed correctly.


Thank you again!!!