Append query only works if data is previously available (1 Viewer)

[saudkazia]

hi

im trying to append some records for the day when the date is selected.

i have a main form tied to the sdate table and a tab that links form called farm using sdate_id. the sdate is an unbound field that is populated to the current date when the form is loaded. The after update of this field is supposed to create a entry for the sdate if it doesn't exist but that is not happening.

Private Sub Form_load()
If DCount("s_date", "sdate", "s_date = #" & Date & "#") = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
s_date = Date

Else
Forms("farm").Form.Recordset.FindFirst " s_date = #" & Date & "#"
End If




when i select the sdate i want to be able to run add_rec to append the records

Private Sub s_date_AfterUpdate()
If DCount("s_date", "sdate", "s_date = #" & Date & "#") = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

DoCmd.OpenQuery "add_rec"
Else
End If
End Sub


the problem is that unless at least a record exists the add_rec query shows 0 records to append. if a record exists it works perfectly

add_rec
INSERT INTO farm ( batch_id, sdate_id )
SELECT DISTINCT batch.batch_id, farm.sdate_id
FROM batch, farm
WHERE (((farm.sdate_id)=[Forms]![farm]![sdate_id]));

sdate_id is a number field that references the sdate table that has sdate_id primary key and sdate date field.

so basically add_rec doesn't work properly because it cannot find an entry for sdate_id because the form is not saved or is dirty (this is my hunch)

Any idea on how to fix it

 

[Ranman256]

Your Dcount is asking for TODAY dates, you want the date on the form,

DCount("*", "tableQuery", "s_date = #" & Me.s_Date & "#")

And incthe sql ,you are asking to match a date ID, (not date)?

 

[The_Doc_Man]

it cannot find an entry for sdate_id because the form is not saved or is dirty

No record exists in the table until the form saves itself or is saved. Therefore, there is nothing to "look up" during the query preparation phase. While technically the form is dirty, all that really means is that the record's .OldValue and .Value properties don't match, at least at the moment.

Oddly enough, if you save the record, it is no longer dirty.