Several Forms with same button with same code opening same form 3

[freespiritcherishes]

Hi everyone and anyone!

I have a several forms each with a button called 'more' that opens the same form called 'moredetails'.

My various forms are called:

TodayView
Future
DiaryList
FUBIFF

These forms reside on a Tab Form called Diary. As I said before, each form has a button called 'more'. This button opens up a 'moredetailsform' giving more details of the record. Using the TodayView form in this example of the underlying code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "moredetails"

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

it naturally worked as it was created by the wizard.

However, I don't want to do the same thing with the other forms cos i am just repeating the code. So i created a function.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "moredetails"

stLinkCriteria = "[DiaryID]=" & Forms!Diary![DiaryID]
DoCmd.OpenForm stDocName, , , stLinkCriteria.

with Diary being the Main Form, and called it to the onClick event of each button on each subform (the ones above). The code runs with no errors but instead of bringing up the corresponding DiaryID of the form its in, it just brings up the last DiaryID entered in the recordset. eg, i could click the more button on record 1712 and it will bring up the more details form for the last record entered being 2856.

What sort of statement would i need to tell access that this procedure has to think about being called various forms in order to bring up the corresponding more details form! So thats one piece of code but to identify a different form when its called at runtime depending on what form I click the button.

Am I talking spaghetti?
freespirit

 

[jiqjaq]

Since all you're doing on the click of the more button is opening another form, why bother creating a separate function? You can open the form in the same line it takes to do the call, plus you'll actually be in the original form to pass the field, so you won't need all those references.

You're always opening the same form and passing the value of a field with the same name. Try this is in the On_Click rather than the function:

DoCmd.OpenForm "moredetails", , , "[DiaryID]=" & Me.DiaryID


And, no, it's not spaghetti but it has more than it's share of meatballs.

Jay

 

[TheAceMan1]

How are ya freespiritcherishes . . . . .

[blue]jiqjaq[/blue] is certainly on target with the one liner . . . . . simple, straight forward and minimal content. However, if you ever have a need to do the same or equivalent with a common routine, here's the Idea and code:

In a module in the modules window, copy/paste the following code:

[blue]Public Sub OpenDetails(CurrentSubForm As Form)
   Dim frm As Form, sfrm As Form, Criteria As String
   Dim frmName As String, sfrmName As String, callName As String
   
   sfrmName = CurrentSubForm.Name [green]'Calling subForm name[/green]
   frmName = CurrentSubForm.Parent.Name [green]'Calling subForm Parent name[/green]
   
   Set frm = Forms(frmName) [green]'calling subForm Parent bject reference[/green]
   Set sfrm = frm(sfrmName).Form [green]'calling subForm object reference[/green]
   callName = "moredetails"
   
   Criteria = "[DiaryID]=" & [purple][b]sfrm![DiaryID][/b][/purple]
   DoCmd.OpenForm callName, , , Criteria

End Sub
[/blue]

The in each button, copy/paste the following:

[blue]   Call OpenDetails(Me)[/blue]

[purple]Cheers![/purple]

See Ya! . . . . . .

 

[freespiritcherishes]

Amazing. Im in awe! And I understand it! Remember in the old body popping days, when the guys would take their roll out mats, doing their popping anywhere in the street and try to burn each other of with the latest moves. That comes to mind! Super Cool Aceman!! Thanx. I know where to come next time! You guys are great.

 

[TheAceMan1]

freespiritcherishes . . . . .

[purple]We are not in competition here.[/purple]

The routine is [blue]just an FYI[/blue] you may find useful at sometime in the future . . . . .

[blue]You take care! . . . . . Ya Hear![/blue]

See Ya! . . . . . .

 

[freespiritcherishes]

Hi, I have this piece of code which appears on the on 'notinlist' event of a button. This button appears 10 times throughout my project. How do i customise the code in a module so that the 'notinlist' event of each button calls a function or a public sub rather than copy this lengthy code for the same button that repeats throughout the project.

The code is:

Private Sub CDDEType_NotInList(NewData As String, Response As Integer)

'DEType on not in list procedure used throughout

Dim strMessage As String
Dim dbsDiary As Database
Dim rstDETypes As Recordset

strMessage = "Would you like to add '" & NewData & _
"' to the list of Diary Entry Types?"

If Confirm(strMessage) Then 'Open the Diary Entry Types Table and add the new data value
Set dbsDiary = CurrentDb()
Set rstTypes = dbsDiary.OpenRecordset("DETypes")
rstTypes.AddNew
rstTypes!DEType = NewData
rstTypes.Update
Response = acDataErrAdded 'Requery the list
Else
Response = acDataErrDisplay 'Display the error
End If
End Sub

As it stands, I created a public sub, with this exact code, but it keeps coming up with 'Text is not in this list' or whatever. DETypes is a the only field in a lookup table. It is Text and is not primary. The combo is set to limit to Yes. Yet if i paste the same code by change to private sub, it works! What do i do, does anyone know?
freespirit

 

[PHV]

'notinlist' event of a button
???
I guess a ComboBox ...
Where is the Confirm function defined ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[freespiritcherishes]

S'ok. Ive sorted. For anyone who is interested,

You can write the values directly to the table with SQL
Make sure that you also make Confirm a Public function
Set the Response parameter in the original NotInList call equal to the return of the new function...

so the original NotInList is replaced by ...

Private Sub CDDEType_NotInList(NewData As String, Response As Integer)
Response = MyNotInList(NewData)
End Sub

and the new generic function is ...
Public Function MyNotInList(NewData As String) As Integer

'DEType on not in list procedure used throughout
Dim strMessage As String
Dim strSQL As String

strMessage = "Would you like to add '" & NewData & " to the list of Diary Entry Types?"

If Confirm(strMessage) Then
'add the new data value directly to Diary Entry Types Table
strSQL = "INSERT INTO DETypes ( DEType ) VALUES ( '" & NewData & "' )"
DBEngine(0)(0).Execute strSQL, dbFailOnError
MyNotInList = acDataErrAdded 'Requery the list
Else
MyNotInList = acDataErrDisplay 'Display the error
End If
End Function

freespirit

 

[TheAceMan1]

freespiritcherishes . . . . .

Have a look here: faq702-5205

See Ya! . . . . . .

 

[freespiritcherishes]

woah!!!! I'll have me a piece of that. Should make for some light reading before beddy byes. Thanks.
freespirit