Syntax for repetitive code

[kforeman]

I have a whole bunch of buttons on a form which open a second form, create, a new record, etc, and fill it out with information from the original form. For instance, clicking button a1b would create a new record in a different table with the data from dropdown a1d and the caption of the label a1l.caption. This code works perfectly so far. However, I need to replicate it and change the a1 to about 100 other identifiers. I've named everything consistently, so b3b matches up with b3d and b3l.caption, etc. I was planning on creating one sub that calls up the form and inputs all the information and then calling that sub from within individual subs for each button. I tried have the sub for each button click be something like

Private Sub a1b_click()
Dim StrName as String
StrName = "a1"
Call OpenNewForm
End Sub

And then in OpenNewForm replacing all the a1l and a3d, etc with StrName. So, referring to the caption of a1l would be [forms]![report]![StrName &_ "l"].caption . But that hasn't worked. I've also tried making it a variant instead. I'm not sure if the first sub that's the problem or the way in which I reference it in the OpenNewForm sub that's common to all the buttons. Any help would be appreciated.

 

[kforeman]

Oh, and I have been able to get it working fine by copying/pasting the main sub and just doing a find replace on it, but the sub is quite long and there would be about 100 of them....I'm trying to keep the code clean and figured this is something I need to learn how to do anyways.

 

[Bat17]

I think you are looking for

[forms]![report]("StrName" & "l").caption

HTH

Peter

 

[kforeman]

Thanks. I'm still getting an error, though. This line gives me a compile error "Expected: list seperator or )" when viewing it:

If DCount("question", "problems", "[report id] = [forms]![report]![report id]" & " AND " & "[question] = [forms]![report]!("StrName" & "l").caption") = 0 Then

I have StrName defined in the sub that opens the above sub. I do not receive an error and neither the Then nor Else commands execute when the sub is called.

 

[Bat17]

Pesky things, Quotes in Dlookup
Try
If DCount("question", "problems", "[report id] = " & [forms]![report]![report id] & " AND [question] = '" & [forms]![report]!("StrName" & "l").caption & "'") = 0 Then

This assumes that [report id] is a number field.

HTH

Peter

 

[Bat17]

oops
If DCount("[question]", "problems", "[report id] = " & [Forms]![Report]![report id] & " AND [question] = '" & [Forms]![Report]("StrName" & "l").Caption & "'") = 0 Then

You had a bang where you did not need it
[forms]![report]!("StrName" & "l").caption
[forms]![report]("StrName" & "l").caption

Peter

 

[kforeman]

Thanks much, Peter, that did fix the syntax problems. However, strName is being considered text, not an actual string. So here's the code I've got thus far:

Private Sub Gas3_change()
OpenProblem "Gas3"
End Sub

Public Sub OpenProblem(strName As String)
    Dim stDocName As String
    Dim stLinkCriteria As String
        If DCount("[question]", "problems", "[report id] = " & [Forms]![Report]![Report ID] & " AND [question] = '" & [Forms]![Report]("strName" & "l").Caption & "'") = 0 Then
'the rest should be right from previous testing, but it doesn't run this far

I get runtime error 2465 stating that the field strNamel cannot be found. I've tried out every way I can think of defining strName without success.

 

[kforeman]

Removing the quotes around "strName" fixed it and it works perfectly now. Thanks to everyone for all the help.