forms based on query - problem with new record

[Kevin_S]

Hi All,

I am having a problem with a form that I have which has a query as its recordsource.

The problem: I have a search form (frmSearch) that lists records in a list box on the form. The unique ID for the records in the listbox is the PositionID. I also have another form (frmTracking) that has a query as its recordsource. The query uses this code in the queries Criteria to filter the record returned to that of the one selected in the frmSearch listbox: Forms!frmSearch!List0

SO basically, the user chooses a record in the frmSearch listbox, double-clicks that record which then (on the double-click event) opens frmTracking with the correct record. Simple enough... However...

THe problem I am running into is that not all of the PositionID's displayed in the listbox will already have a tracking record. The ones that do not I want to have the form open to a new record and transfer the PositionID over to the frmTracking. Currently, the form opens blank (no textboxes or nothing) because the WHERE criteria is null and because there was no positionID found in the tracking table so there is nothing to display.

Does anyone know a way to get the form to recognize there is not record established in the tracking table and create a new one for the Position ID selected in the listbox?

Any/All help greatly appreciated!
Thanks-Kevin

 

[Rob.Mills]

What you could do is setup a lookup expression in the command button's event. Something like this:

If Not IsNull(DLookUp("[ID Field]","[Table query is bound to]","[ID Field]=" & Forms!frmSearch!Listbox)) Then

DoCmd.OpenForm "frmTracking", , ,"[ID Field]=" & Forms!frmSearch!Listbox

Else

DoCmd.OpenForm "frmTracking", , , ,Add Mode

End If

 

[Kevin_S]

Hi Rob -

I used to use a method similar to the one you provided for assistance and it worked really well when I have the table as the forms record source and I was filtering for a record but in this instance I am already filtering the form before it opens by adding criteria in the WHERE statement so i needed a different approach.

For those interested and for anyone with a future problem that is similar, I was able to resolve this by using a DLookUp statement to try and find a match in the tracking table for the position ID in the frmSearch listbox - if there was a match then I simple opened the form and exited the code as the tracking forms WHERE statement would find the record I was interested in. For any case where the was not a PositionID in the tracking table I then inserted a SQL string that would append the position ID into the tracking table as a new record - then I opened the tracking form and exited the code. Once the Position ID was appended into the tracking table the code for the table was able to find the record and open.

Thanks for you help Rob -
Kev