Help I think I'm using Bad Code

[office_guru]

I'm discovering VB is so not my thing and I'd be lost without you all! Having said that, Please Help!

I had previously had a problem of opening a record where a record didn't exist on the same form - my code was sited as interesting code by one poster ;-) however it does work. Well now I'm trying to do something similiar but via multiple forms and now I'm having problems again.

I would like to open Form B (frm_pocs_review) from a link in Form A (frm_company_review) based on a common field (Company) on each form. If the record does not match want a message box to come up, so I can avoid the blank form (it's a no addition form so it comes up completely blank with no buttons/links to main menu), no record create new? Got that to work. If the record does match I want Form B to open on that record. - That's not working it's defaulting to the first record on Form B.

Can someone tell me where I'm going wrong? - Sorry in advance if the code in pain stakingly awful. :-/

Private Sub Label107_Click()
Dim rs As Object

Dim x
x = Me!Company

Dim frm As Form
DoCmd.OpenForm ("frm_POCs_Review")
Set frm = Forms!frm_POCs_Review

Set rs = frm.Recordset.Clone
rs.FindFirst "[Company] = '" & x & "'"

If rs.nomatch Then
Dim bytResponse As Byte
bytResponse = MsgBox("This record does not exist, would you like to create it?", vbYesNo + vbExclamation, "Not In List")
If bytResponse = vbYes Then
Response = acDataErrAdded
stDocName = "frm_pocs_input"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
datefield = NewData
DoCmd.Close acForm, "frm_pocs_review"
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue 'Show No message

End If

Else
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company] = '" & x & "'"


End If

DoCmd.Close acForm, "frm_company_review"

End Sub

Thanks!

 

[john471]

You code could do with some improving, but I won't comment on that, being far from perfect myself....

The answer to your question

If the record does match I want Form B to open on that record. - That's not working it's defaulting to the first record on Form B.

lies in THIS SECTION

Else
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company] = '" & x & "'"


End If

But you probably knew that much too.
You have set rs to be a clone of the recordset of the current form. When you
rs.FindFirst "[Company] = '" & x & "'"
you are simply playing with that cloned recordset, and having no effect on the intended form.

I believe you may want to read up on using Bookmarks, but fair-warning - I'm told they can be un-reliable in crtain circumstances.

I use a more complicated approach, which I won't discuss unless provoked

Someone Else may be able to provide more illumination ????

 

[office_guru]

Consider yourself provoked

John471 - Thanks for replying. I'll look into bookmarks, I'm not familiar with them.

I'd be happy and open to hearing your more complicated approach if you think it'll work (and I can actually implement it!).

or anyone elses ideas are more than welcome as well!

Thanks!

 

[office_guru]

works

I went back to my else statement as you pointed out thats where my problem is and replaced it with:

Else
stDocName = "frm_pocs_review"

stLinkCriteria = "[Company]=" & "'" & Me![Company] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frm_company_review"

End if

And now it works! Think I was looking at it too long when I was originally working on it. Thanks again!

 

[john471]

so then you've got your frm_pocs_review doing something with its openArgs ?

Good for you. Glad you got it working