Find matching record in a table based on OpenArgs of a form with 1 to 1 relationship

[fire2ice]

Here is what I'm trying to accomplish. I have one form with a button on it that calls another form to add additional information. I want to be able verify that there is not already data entered for the specific record. The reason it is a separate form and not a subform is that the data in the 2nd form can be independent of the data on the first (although there is a 1-to-1 relationship between the underlying tables, each can have records the other does not).

To make this more clear...I have a form to enter different events. Another form is used to track materials distributed. However, sometimes materials are distributed at events. I want to be able to link the materials to the events if materials are sent to the events, but be able to enter materials separately if materials are sent to people. In addition, not all events have materials distributed. When clicking on a button to call the materials form, I want it to first check to make sure that there is not already a materials record for the event. If a record does already exist, I want to be able to view it in case additions or corrections are needed. The tables are linked by an ID (with that ID being passed from the event form to the materials form via OpenArgs when the materials record is created).

With that being said, I created code that copies certain data from the events form and parses it to the materials form using OpenArgs. The OpenArgs part works (with thanks going to RuralGuy for his help). However, I can't seem to get the find part to work to check whether materials already exist for an event. Below is my code. What the code does is the part after the find which places values in textboxes. It seems to skip over any searching. If it does search, then it skips the displaying part and goes directly to creating a new record with the parsed data from the OpenArgs. I want this to be one or the other, not both. Either it displays the matching record or creates a new record with the parsed data.

Thanks in advance.

-------------------------------------------------------------------------------------------------------

Private Sub Form_Load()

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
Args = Split(Me.OpenArgs, ";")
If Not IsNull(Args(6)) Then
Dim strID As String
strID = Args(6)
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst "EventID = '" & _
strID & "'"

If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
Exit Sub

End If

End If

Me.EventName = Args(0)
Me.EventDate = Args(1)
Me.Zip = Args(2)
'Me.ZipPlusFour = Args(3)
Me.City = Args(4)
Me.State = Args(5)
Me.EventID = Args(6)

End If

End Sub

 

[wazz]

you have:
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
Exit Sub
but you haven't actually bookmarked your RS.

RS.FindFirst "EventID = '" strID & "'"
becomes
RS.bookmark = RS.FindFirst "EventID = '" strID & "'"

something like that.

 

[fire2ice]

That creates a compile error. It says: Expected end of statement.

 

[wazz]

sry, just air-coding. i could be off on this. if you don't hear from someone soon try to post a stripped down version of your db with some of the necessary form and code and data so we don't have to write it all up. you'll prob hear from someone before that though. the answer seems to be staring me in the face...but i gotta go.

 

[wazz]

hi again. been playing with this and seems to work beautifully. if it's skipping the search it would seem you are not passing enough args or for some reason arg(6) IS null. don't forget, even though you have commented-out 'Me.ZipPlusFour = Args(3) you still need to pass seven args for the code to work as-is, or adjust the arg-numbers.

 

[fire2ice]

I am attaching the db with all data stripped. All OpenArgs are accounted for (I'm passing them all through but not capturing the ZipPlusFour on the receiving end as I didn't put it in the proper table yet. Args(6) shouldn't be Null as it is an autogenerated number and the primary key for the originating table.

Hopefully seeing the working db will help reveal the problem.

Thanks.

 

[wazz]

it worked for me. i had to jump through a few hoops to get there but the openargs part does work. after entering a date i was thrown around to some subforms for no apparent reason and had to cancel those actions/events, but once i got some data finally entered and went to the materials form the proper fields were filled in...

 

[fire2ice]

wazz...The OpenArgs part is what I said works perfectly. What doesn't work is the part to check whether materials already exist for an event. I do not want to create two sets of materials records for the same event.

 

[wazz]

ok, sry about that. played with it some more. there are several intertwined issues going on. three of the biggest are:

- the code you supplied is looking for a string when the ID is a number (long);
- your forms (including the event form) are opening in 'Add' mode, or data entry mode, so they will never show all records - they will always open at a new record;
- the code is not looking for a **unique** ID, so it will stop looking at the first one it comes to. this is probably the biggest problem and you won't get around it without some redesign.

hth.

 

[fire2ice]

Okay...Let's look at each of these separately.

1) I changed the Dim from String to Long....Done

2) I want to open the form in 'Add' mode. So, what I need to do is write the code to say open in 'Add' mode only if no match is found. Thus, I need to change the button on frmEvents that is used to trigger the opening of frmMaterialsRequests to open in acFormEdit mode to look for matches. If no matches are found, then I need the code to change it to acFormAdd. Would this be the solution? If this is the solution, how would I keep the OpenArgs when I change to acFormAdd?

3) I'm not looking for a unique ID specifically. What I'm looking for is to check to see if a record already exists with that ID. If so, it should open the form to that record for editing. This should hopefully eliminate creating duplicates in the first place. I'd really like to make the EventID on frmMaterialsRequests an Inexed (No Duplicates) field, but I was getting an error when I had that property set. It told me that there was an error with the primary key or indexed key (or something like that).

Hopefully I'm on the right track and just need a little push.

Thanks.

 

[jsv2002]

Hi fire2ice,

Bit late now but will post in case it trips anyone else up (like me for about 4hrs thats how I came across the post!!)

You need to search for duplicates before you load the form in the on click event of the command button that is going to call the second form with something such as DCount,

Maybe:

Dim stDocName As String
stDocName = "frmTest"

'Checks the table for duplicate entries prior to passing OpenArgs array if true goes to In List Sub.

If DCount("*", "tblTest", "[FormID] = " & Me.FormID) = 0 Then
Else
GoTo In_List_Click

End If

'Fire your form here as no duplicate found.....

DoCmd.OpenForm stDocName, , , , acAdd, acDialog, [FormID]

In_List_Click:

MsgBox "This PPE Item already has a Risk Assessment" _
& vbCr & vbCr & "This command will end", vbInformation _
, "Duplicate information entered..."

Exit Sub

Ahh well as said late but might help someone have less of an headache than me...

Cheers John

P.S. You can also use the DCount to compare multiple fields in the table if required...