Append query problem

[Bruce75]

Hello

I am having a problem with an append query. I want my database to create a single new record in a table using values from another. I guess an append query is used for this.

I have this so far:
INSERT INTO jun_waitingevent ( waitinglistevent_ID, offdate, waitinglistIDFK, patientIDFK )
SELECT jun_waitingevent.waitinglistevent_ID, jun_individualevent.startdate, jun_individualevent.individualIDFK, jun_individualevent.patientIDFK
FROM (tbl_patientdetails INNER JOIN jun_individualevent ON tbl_patientdetails.patientID = jun_individualevent.patientIDFK) INNER JOIN jun_waitingevent ON tbl_patientdetails.patientID = jun_waitingevent.patientIDFK
WHERE (((tbl_patientdetails.patientID)=[Forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![patientIDFK]));

It is trigger in an after update event on a form. The form consists of a main form with three subforms on tabs. The user enters a start date for therapy, then, if no matching waiting list is available for that specific patient (I have a bit of code that works this out), the append query is run. It is meant to take the start date of the therapy and create a new record of a waiting list event, with the start date of therapy becoming the date when the patient was removed from the newly created waiting list event. The append query is also meant to take the therapy type foreign key (individualIDFK) and use it as the therapy type foreign key in the waiting list event table (waitinglistIDFK).

Every time the above query is run, it appends 0 records. What am I doing wrong? Am I an idiot?

thanks

Bruce

[ajetrumpet]

I'm not quite sure I understand everything your doing from your description Bruce, but take a look at this thread....http://www.access-programmers.co.uk/...d.php?t=134435

it kind of sounds like you're trying to do the same thing that this person was, although a bit more complext in nature....

[Bruce75]

thanks.
I am a bit of a beginner but I dont think that is what I am after.

I am building a patient database to record patient history in our service.
The tables of importance, I think are:

tbl_patientdetails
patientID
name, etc

tbl_treatment
treatmentID
treatment
group (yes/no)

Junction tables:
jun_waitingevent
waitingeventID
treatmentIDFK
patientIDFK
ondate (date on waiting list)
offdate

jun_individualevent
individualID
treatmentIDFK (related to tbl_treatment.treatmentID)
startdate
enddate

I have a form with two subforms on tabs. I want it so that:

(1) when the user enters a start date for treatment, the corresponding waiting list is updated by using the startdate of therapy as the end date of the waiting list event. So, if someone is on the waiting list for CBT, and they start CBT, their waitinglist event for CBT is updated so that the start date of the therapy becomes the end date of their time on the CBT waiting list. Now, I have done this with an update query and it seems to be fine.

(2) when the user enters a start date for therapy, and there is no corresponding waiting list event (E.g. someone starts CBT, but they have not been on waiting list for CBT - maybe because of error, or because the database has not been sufficiently back dated), a new record is created in jun_waitinglist for that patient that copies across the appropriate details that the user has entered for the start of therapy. For example, a patient starts CBT without them having a record of being on the CBT waiting list, a new record is automatically created showing that they had been on the CBT waiting list.

This is where I am having big problems. I am trying to use an append query to do this, but I cant figure out how to select only the patient currently shown on the form, and specifically, has not got a matching entry in their waitinglist form (they have started CBT but have no record of being on the CBT waiting list), or has no entries at all, and then appending jun_waitinglist to reflect the data that has been entered on jun_individualevent.

Phew. I doubt anyone has the stamina to have got this far through my post. I also doubt whether my post makes much sense.

Any help is greatly appreciated. I am getting very frustrated and have taken to banging things with my fist.

thanks

Bruce

[Bruce75]

Hello again

I have tried this in vba:

Private Sub StartDate_AfterUpdate()
Dim starttherapy As Variant
Dim therapytype As Variant
Dim UResponse As Integer
Dim pid As Variant

Const conNullError = 94

starttherapy = Me!startdate
therapytype = Me!Type_of_Treatment
pid = Me!patientIDFK
UResponse = MsgBox("Do you want to remove the patient from the appropriate waiting list?", vbYesNo, "Go to waiting list?")

If UResponse = vbYes Then

Forms![frm_patientdetails].SetFocus
Forms!frm_patientdetails.Form!tc.Pages("Waiting Lists").SetFocus
Forms!frm_patientdetails.Form!sf_jun_waitinglist.S etFocus

therapywait = Forms!frm_patientdetails!sf_jun_waitinglist.Form!W aitingfor

If IsNull(therapywait) Or therapywait <> therapytype Then
MsgBox ("No record of patient on waiting list")
DoCmd.RunSQL "INSERT INTO jun_waitingevent ( patientIDFK, waitinglistIDFK, offdate )" & _
"SELECT pid, therapytype, starttherapy " & _
"FROM jun_individualevent " & _
"WHERE (((jun_individualevent.patientIDFK)=[Forms]![frm_patientdetails].[patientID]));"
Forms!frm_patientdetails!sf_jun_waitinglist.Requer y
Forms!frm_patientdetails!sf_jun_waitinglist.Form!W aitingfor.SetFocus
Else

DoCmd.OpenQuery "update_waitoff"
End If

Else
Forms!frm_patientdetails!sf_jun_individualevent.Fo rm!Type_of_Treatment.SetFocus
End If


End Sub

I thought if I obtain the patientID, type of therapy and start date of therapy information, I would get information for just one record, which I could then use in an embedded SQL. However, the code keeps prompting me for the parameter values whenever I run it, rather than use the figures I thought I had stored earlier.

Bugger it... what I am doing wrong?

thanks

Bruce

[ajetrumpet]

Originally Posted by Bruce75

when the user enters a start date for therapy, and there is no corresponding waiting list event, a new record is created in jun_waitinglist for that patient that copies across the appropriate details that the user has entered for the start of therapy. For example, a patient starts CBT without them having a record of being on the CBT waiting list, a new record is automatically created showing that they had been on the CBT waiting list.

Bruce, did you look at the thread link above?? I still think that would work, and you probably wouldn't need to do much else. If you don't think that is the right way to go, why don't you post the SQL you have written to accomplish #1 (the append query that IS working for you).

Seeing that would definately help determine why this seems complicated....

nevermind I guess....

[ajetrumpet]

Do you have any queries in your database, or are you trying to do all of this in a module?? If you have a query, please post just the SQL you have in the query...or is the code you posted above the only snippet you are using??

[Bruce75]

Thanks ajetrumpet

I am trying to do all the database in vba and queries.

the update query sql that works is:

UPDATE jun_waitingevent, jun_individualevent SET jun_waitingevent.offdate = [forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![startdate]
WHERE (((jun_waitingevent.offdate) Is Null) AND ((jun_waitingevent.waitinglistIDFK)=[Forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![individualIDFK]) AND ((jun_waitingevent.patientIDFK)=[Forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![patientIDFK]));

This just updates a missing off date value with the start date of the therapy just entered.

The code I posted is a later attempt when I could get the append query to do what I wanted. I thought the code might be a way round it, but the variables pid, starttherapy therapytype do not pass their values to the embedded sql statement.

I may have misunderstood the linked post, so I will read it again. Sorry to be a pain.

Cheers

Bruce

[ajetrumpet]

Originally Posted by Bruce75

UPDATE jun_waitingevent, jun_individualevent SET jun_waitingevent.offdate = [forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![startdate]
WHERE (((jun_waitingevent.offdate) Is Null) AND ((jun_waitingevent.waitinglistIDFK)=[Forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![individualIDFK]) AND ((jun_waitingevent.patientIDFK)=[Forms]![frm_patientdetails]![sf_jun_individualevent].[Form]![patientIDFK]));

This just updates a missing off date value with the start date of the therapy just entered.

This is what I can think of Bruce....

INSERT INTO [relevant fields that MAY be missing data by error]
SELECT [fields from forms where new data may have been entered]
FROM [source tables of new field data]
WHERE [selected fields] NOT IN (related fields of the target table)

This would be good if there is no startdate in your waiting list table. This is only for records that do not have data in the waiting list table. This does not accomplish the process you want in one step. Just thought I would throw it out there.

It might be a good idea to post a file so people could get an idea of what you are talking about. It wouldn't hurt...

[Bruce75]

Thanks ajetrumpet... i will try your suggestion and post my db.

Best wishes

Bruce

[Bruce75]

I am such an idiot. I spent 6 hours trying to solve this problem and the solution was very simple. I just added the primary key of the source table to the query that the subform is based on, then included a form reference to it in the append query criteria.

thanks for all your help though!

[ajetrumpet]

That's funny....