From: Jen on 6 Dec 2009 12:35 I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String DoCmd.OpenForm "frmScheduleCallBack", , , , , , Me.CompanyID Then on the other form that opens on the load event I have this code that runs: This checks to see if there is a related record and display it if it finds one. If there is no related record it is ready to insert a new record. I keep getting a datatype mismatch error on line 6 (below). The companyID in the company table is an autonumber. I know that I can't compare a long integer to a string and get a match but how do I handle this problem?? 1Private Sub Form_Load() 2Dim strCompanyID As String 3 If Not IsNull(Me.OpenArgs) Then 4 strCompanyID = Me.OpenArgs 5 With Me.RecordsetClone 6 .FindFirst "[CompanyID] = '" & strCompanyID & "'" 7 If .NoMatch Then 8 DoCmd.GoToRecord , , acNewRec 9 Me.txtCompanyID = strCompanyID 10 Else 11 Me.Bookmark = .Bookmark 12 End If 13 End With 14 End If End Sub
From: Al Campagna on 6 Dec 2009 13:51 Jen, A subform, by itself... when associated to a main form via the Parent/Child relationship... will do what you ask without any coding at all. Example: If I open (the sub could be just hidden) frmActivities for Smith & Company (CompanyID = 1425) and there are no activity records associated with that CompanyID, the subfrom will open with no records displayed... and sitting on a New record, awaiting your data entry. From your description that's should do what you want... >I am trying to link to forms. That's what a main form and subform, related by some unique key value, are for. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Jen" <Jen(a)discussions.microsoft.com> wrote in message news:2E798981-1A1E-419E-A52A-05D06C50EA66(a)microsoft.com... >I am trying to link to forms. I have a button on the main form > (frmCompanies) that needs to open another form when a button is clicked. > The > new form that opens is frmScheduledActivities. When it open it needs to > check > to see if there is an existing record (scheduled activity) for the company > and if there is no scheduled activity be set to add a new record. > > When the button is clicked on the main company form (on the on click > event) > this code runs: > Private Sub cmdCallBack_Click() > On Error GoTo Err_cmdCallBack_Click > > Dim stDocName As String > Dim stLinkCriteria As String > DoCmd.OpenForm "frmScheduleCallBack", , , , , , Me.CompanyID > > Then on the other form that opens on the load event I have this code that > runs: > This checks to see if there is a related record and display it if it finds > one. If there is no related record it is ready to insert a new record. > > I keep getting a datatype mismatch error on line 6 (below). The companyID > in > the company table is an autonumber. I know that I can't compare a long > integer to a string and get a match but how do I handle this problem?? > > 1Private Sub Form_Load() > 2Dim strCompanyID As String > > 3 If Not IsNull(Me.OpenArgs) Then > 4 strCompanyID = Me.OpenArgs > 5 With Me.RecordsetClone > 6 .FindFirst "[CompanyID] = '" & strCompanyID & "'" > 7 If .NoMatch Then > 8 DoCmd.GoToRecord , , acNewRec > 9 Me.txtCompanyID = strCompanyID > 10 Else > 11 Me.Bookmark = .Bookmark > 12 End If > 13 End With > 14 End If > > End Sub >
From: Daryl S on 7 Dec 2009 14:19 Jen - Al's subform solution would be the way I would go. So you know, your error comes from putting single quotes around the companyID field. You indicated this is an AutoNumber field, which is a long integer, not text. -- Daryl S "Jen" wrote: > I am trying to link to forms. I have a button on the main form > (frmCompanies) that needs to open another form when a button is clicked. The > new form that opens is frmScheduledActivities. When it open it needs to check > to see if there is an existing record (scheduled activity) for the company > and if there is no scheduled activity be set to add a new record. > > When the button is clicked on the main company form (on the on click event) > this code runs: > Private Sub cmdCallBack_Click() > On Error GoTo Err_cmdCallBack_Click > > Dim stDocName As String > Dim stLinkCriteria As String > DoCmd.OpenForm "frmScheduleCallBack", , , , , , Me.CompanyID > > Then on the other form that opens on the load event I have this code that > runs: > This checks to see if there is a related record and display it if it finds > one. If there is no related record it is ready to insert a new record. > > I keep getting a datatype mismatch error on line 6 (below). The companyID in > the company table is an autonumber. I know that I can't compare a long > integer to a string and get a match but how do I handle this problem?? > > 1Private Sub Form_Load() > 2Dim strCompanyID As String > > 3 If Not IsNull(Me.OpenArgs) Then > 4 strCompanyID = Me.OpenArgs > 5 With Me.RecordsetClone > 6 .FindFirst "[CompanyID] = '" & strCompanyID & "'" > 7 If .NoMatch Then > 8 DoCmd.GoToRecord , , acNewRec > 9 Me.txtCompanyID = strCompanyID > 10 Else > 11 Me.Bookmark = .Bookmark > 12 End If > 13 End With > 14 End If > > End Sub >
|
Pages: 1 Prev: How to make Access 2007 Navigation pane invisible? Next: Display Search Results in Form |