Help needed with IF THEN (1 Viewer)

[linskill]

I am trying to use a VB IF THEN statement to update a field in another table based on the column value of a combo box. i.e.
If stakeid =1 THEN tblTrials

ud[field = combobox.column3 WHERE trialsIID=" & trialsID
Can anyone help me out with the correct syntax, I cant seem to get it right.
Thanks

 

[jdraw]

Since the combo box will be on a form, can you tell us more about your tables and the form involved?

Hopefully you don't have a combo in a table.

 

[linskill]

Thanks for the quick reply,
I have a form bound to the trials table, within that form is a subform bound to the judges table. In that subform is a combobox that is getting its values from a lookup table(Stakes). I need to take the stakeid from the combo box and store it into the Judges table (so far so good) and I then need to take the Stake abbreviation(stakeabb) and store it into the relevant field in the trials table i.e. UD.
I hope this is clearer.
Thanks

 

[jdraw]

Can you post a dumbed down version of your database (no confidential data)? And describe exactly what to look for, what you want back etc.

 

[linskill]

I have attached a stripped down version of the database, as i said earlier I am trying to use an after update event on the stakecombo to populate the relevant field in the trialsT table,
Thanks for looking

 

[jdraw]

I'm looking at your tables and relationships. You do not have any referential integrity set up. Not all of your tables are shown in your relationships window. Is there some reason for this?

In a few sentences what exactly is the purpose of the database?

I really think you should work to get the tables and relationships set up with referential integrity according to your business facts.

 

[linskill]

I think we are in danger of going off topic here, The full version of the db has many more tables with referential integrity in place where needed.

This is what I am trying to do but I can't seem to get the syntax correct.

Private Sub StakeCombo_AfterUpdate()
If StakeID = 2 Then
trialst.TD = "TD" "stakecombo.trialID=" & trialid
End If
End Sub

I hope this makes sense.
Have you any suggestions?
Thanks

 

[gemma-the-husky]

I think the point is that it is hard to understand why selecting a row in a conbo box should cause additional actions based on a particular column value. This is distinctly unusual.

things like that point to normalisation problems

 

[linskill]

I agree it is not normal, its actually a fudge repair of my short sightedness when I originally created this database, I wish to use the trials table via MySQL on my website. As I am a bit more familiar with Access then PHP I felt the easiest way to do this was to repeat a small amount of data which will enable me to get the values I require in one record.

This it what I am presently able to generate:

SELECT  TrialsT.TrialID, SocietiesT.Society, TrialsT.Status, TrialsVenue.VTown,  TrialsT.EndDate, TrialsT.TrialVenueID, TrialsT.SocID, StakesT.Abbrev
FROM  (((TrialsVenue INNER JOIN SocietiesT ON TrialsVenue.SocID =  SocietiesT.SocID) INNER JOIN TrialsT ON (TrialsVenue.TrialvenueID =  TrialsT.TrialVenueID) AND (SocietiesT.SocID = TrialsT.SocID)) INNER JOIN  JudgesT ON TrialsT.TrialID = JudgesT.TrialID) INNER JOIN StakesT ON  JudgesT.StakeID = StakesT.StakeID
GROUP BY TrialsT.TrialID,  SocietiesT.Society, TrialsT.Status, TrialsVenue.VTown, TrialsT.EndDate,  TrialsT.TrialVenueID, TrialsT.SocID, StakesT.Abbrev;

This gives me a multiple records for each trial depending on the amount of stakes(class) held

This is what I am trying to achieve:

SELECT  TrialsT.TrialID, SocietiesT.Society, TrialsT.Status, TrialsVenue.VTown,  TrialsT.EndDate, TrialsT.TrialVenueID, TrialsT.SocID, TrialsT.CD,  TrialsT.UD, TrialsT.WD, TrialsT.TD, TrialsT.PD, TrialsT.Vet,  TrialsT.Intro
FROM (TrialsVenue INNER JOIN SocietiesT ON  TrialsVenue.SocID = SocietiesT.SocID) INNER JOIN TrialsT ON  (TrialsVenue.TrialvenueID = TrialsT.TrialVenueID) AND (SocietiesT.SocID =  TrialsT.SocID)
GROUP BY TrialsT.TrialID, SocietiesT.Society,  TrialsT.Status, TrialsVenue.VTown, TrialsT.EndDate,  TrialsT.TrialVenueID, TrialsT.SocID, TrialsT.CD, TrialsT.UD, TrialsT.WD,  TrialsT.TD, TrialsT.PD, TrialsT.Vet, TrialsT.Intro;

This gives me one record per trial held with the stakes included.

I hope this makes sense. If anyone has a better suggestions I am all ears
Thanks

 

[linskill]

I've sorted it, seems my problem was trying to update directly to the table, adding a few hidden text fields to the form worked a treat.
Thanks for your patience and all your input.