A query on queries

[JamieW]

Hi, i have been using the forums for a while now but this is my first post.. there's some very helpfull information on here but i have yet to find an answer to my query by using the search.

I'm wondering if it is possible to use an update query to update a single record in a field.

I have a datasheet subform within a form... I need one of the fields (yes/no tick box) to populate data into a different field in the same record once ticked.

I have succeeded with a simpler design by populating any 'is null' cells when tick box is 'true' but this only worked when your datasheet gets filled completely from the top down.

I need to be able to ignore any empty records in the same field, and only perform the update on the cell in the same record as i clicked.

Sorry if that is confusing, any help/ examples would be greatly appreciated.

Thanks, Jamie

 

[Arfur Fuxache]

Thought about using an iif ?

 

[GaryPanic]

Lets see if we can sort this one
in your view I presume you will select 1 row (this will have a unique ref on it some how yes?)
great this is not the tidiest way of doing this but have a field on your form tied to this record uniqu identifier so when you select it in the field on your form the unique id is in it - great now have an update qry on this field on your form push a button and update - now all you need to do is worry about is the refresh option as this will update then you need to go out of the form and back into it to refresh ( this may not work but its a alternative view )
I have something similar but its not with datasheet views

 

[JamieW]

cheers for the responses. I'm not too sure about IIR's but will do some research into them.

Let me try and explain myself better, maybe this pic will help.

Basically, i want to be able to click STN1, STN2, STN3 ect to populate the current date into their respective STNDate columb...

is there also a method i could use to link the STN's to the STNDefenition table??

This would help me input to the data log. for eg.

if STN1 was ticked for Line-20000; i want the last record of the log to read | LINE-200000 | PIDNoRev | XX.XXpm | 10 |

I think i can manage writing to the tblLog as i can write to the 'is null' record.

i Really need to know how to single out a specific date record and not to fill the whole field with the input value

 

[JamieW]

partially sorted the date entry:

Private Sub Form_AfterUpdate()

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE [tblMainProgressList] SET [tblMainProgressList].STN1Date = Now() WHERE ((([tblMainProgressList].STN1Date) Is Null) AND (([tblMainProgressList].STN1)=True));"

DoCmd.RunSQL "UPDATE [tblMainProgressList] SET [tblMainProgressList].STN1Date = Null WHERE ((([tblMainProgressList].STN1Date) Is Not Null) AND (([tblMainProgressList].STN1)=False));"

DoCmd.SetWarnings True


End Sub

although this requires me to click elsewhere on the form to update 'after update'

it would be good to have it update as i click the tick box... any ideas?


I still need to figure out how to link the tables together. ie. STN linked to the STN defenition table.

any help would be greatly appreciated!

Thanks, Jamie