tabular subform

[juicybeasty]

I've put this post in the forms forum and didn't get a reply, so I'm thinking it may be more of a VBA question:

I have a tabular subform linked to a main form. The main form (not tabular) has fields including TrainingNeed, RequiredScore and ActualScore. If there is a deficit between RequiredScore and ActualScore I want the text in the TrainingNeed text box to be automatically sent to a NEW LINE in the tabular subform.

I know how to transfer the data to the subform using the AfterUpdate event.

But in the tabular subform it doesn't create a new row for each new TrainingNeed - is it possible to specify that you want it to start a new line?

I'm probably doing something obviously wrong, or maybe it's more complicated than it seems. Either way, I would really appreciate someone's expertise on this!

 

[bjackson]

is the data in the subform stored in a table

 

[juicybeasty]

it is stored in a table, yes

 

[juicybeasty]

it is stored in a table, yes

 

[bjackson]

each line you refer to is a record from the table so to get
a new line you have to add a new record

when you say you know how to transfer the data are
you adding a new record to the table and if so are
requerying the subform to display the latest data

 

[juicybeasty]

No, I haven't done it that way, was just doing it at form level (if that makes sense).

How would I go about doing that with code?

 

[bjackson]

Sorry cany quite follow what you are trying to do
could you attach your db

 

[juicybeasty]

I was not able to post the original version, so I've built a replica to illustrate the point I'm trying to make.

The replica I built is in A2002 because that's what I have at home - but the original I built at for/work in A97 - I'm aware this may make a difference to it's functionality.

In brief, what I want to achieve is:

- when the value in the Deficit box on fsubDetails (which in the original is a calculated field) is > 0, the value in the TrainingArea text box (eg Access) should be transferred to the TrainingCourse text box, in a new row, in the fsubTraining subform (and stored in the associated tblTraining).

I suggest this could be achieved by putting the appropriate code in the AfterUpdate events of both RequiredScore and ActualScore, so that each time the value in one of these is updated, the Deficit value would be calculated, and then the appropriate text would be transferred to the TrainingCourse text box in fsubTraining.

Unfortunately, I'm not sure what this code should be!

Please help...

 

[bjackson]

hope this points you in the right direction
regards bjackson

 

[juicybeasty]

thanks very much, that's really great. now I just have to work out what you did, but that's exactly what I wanted.

I assume it'll work in A97 bcos you used DAO.

thanks again.

 

[bjackson]

it should work in 97, all that happens is in the afterupdate
of the actual score,it adds a record to the table if no
matching record is not there,or if there is a matching record
it edits that record,thats why i added the extra field in the
table

 

[juicybeasty]

Sorry to drag this one up again, but I've been trying to recreate the syntax in access 97 and I'm getting an error.

Can anybody see what's the matter with this line of code?


Set Qdf = dbs.CreateQueryDef("", "SELECT tblHIBOpsTraining.TrainingArea, _
tblHIBOpsTraining.ID, tblHIBOpsTraining.FormNo" _
& " " & "FROM tblHIBOpsTraining" _
& WHERE (((tblHIBOpsTraining.TrainingArea)=" & "" "" & OpsUTCN & """" & " _
) AND ((tblTraining.ID=" & OpsUTCID & "))

Thank you.

 

[juicybeasty]

Sorry to drag this one up again, but I've been trying to recreate the syntax in access 97 and I'm getting an error.

Can anybody see what's the matter with this line of code?


Set Qdf = dbs.CreateQueryDef("", "SELECT tblHIBOpsTraining.TrainingArea, _
tblHIBOpsTraining.ID, tblHIBOpsTraining.FormNo" _
& " " & "FROM tblHIBOpsTraining" _
& WHERE (((tblHIBOpsTraining.TrainingArea)=" & "" "" & OpsUTCN & """" & " _
) AND ((tblTraining.ID=" & OpsUTCID & "))

Thank you.

 

[bjackson]

try this

Set QDF = dbs.CreateQueryDef("", "SELECT tblHIBOpsTraining.TrainingArea, tblHIBOpsTraining.ID, tblHIBOpsTraining.FormNo" _
& " " & "FROM tblHIBOpsTraining" _
& " WHERE (((tblHIBOpsTraining.TrainingArea)=" & """" & OpsUTCN & """" & ") And ((tblTraining.id = " & OpsUTCID & ")")

 

[juicybeasty]

thanks v much for having another look at it.

the original one worked ok after fiddling about with it a bit. i think what i'd done was put line separators in where you hadn't which is what confused it! i only worked it out about an hour ago after worrying all night.

anyway, thanks again, it works in my project and i've learnt quite a lot about recordsets, etc, which i knew nothing about before.