need help writing a DLookup statement "Mark Kubicki" <mark.kubicki[ at ]verizon.net> 12/22/2008 2:25:45 PM on a form <FixtureCataloges>, i have 2 fields that uniquely identify the record: [Manufacturer] and [CatalogNumber]; also on the form is a subform with 1 field: [Options], whose data source is a table, FixtureCatalogsLuminiareTypes, the table has 3 fields [Manufacturer] [CatalogNum] and [Option] (-spelled differently... too many people woking on the same...); and is related to the data source used for the main form with fields: [Manufacturer] and [CatalogNum] when a user goes to the subform, i'd like to check is a value 'accent light' is present in the subform's data source i've written the following code, but it's not working (i get error '2428' invalid domain aggregate...) any suggestions would be greatly appreciated Private Sub Options_GotFocus() varX = Null Me.Options.Requery varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ "[Option] = 'accent light' and [Manufacturer] = " & Forms![FixtureCataloges].Manufacturer _ & " and [CatalogNum] = " & Forms![FixtureCataloges].CatalogNumber) If IsNull(varX) Then ... Else ... End If End Sub thanks in advance, Mark

RE: need help writing a DLookup statement RonaldoOneNil 12/22/2008 2:43:01 PM Put quotes round your domain as well varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... "Mark Kubicki" wrote: [Quoted Text] > on a form <FixtureCataloges>, > i have 2 fields that uniquely identify the record: [Manufacturer] and > [CatalogNumber]; > > also on the form is a subform with 1 field: [Options], whose data source is > a table, FixtureCatalogsLuminiareTypes, > the table has 3 fields [Manufacturer] [CatalogNum] and [Option] > (-spelled differently... too many people woking on the same...); > and is related to the data source used for the main form with fields: > [Manufacturer] and [CatalogNum] > > > when a user goes to the subform, i'd like to check is a value 'accent light' > is present in the subform's data source > > > i've written the following code, but it's not working (i get error '2428' > invalid domain aggregate...) > any suggestions would be greatly appreciated > > Private Sub Options_GotFocus() > varX = Null > Me.Options.Requery > varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ > "[Option] = 'accent light' and [Manufacturer] = " & > Forms![FixtureCataloges].Manufacturer _ > & " and [CatalogNum] = " & Forms![FixtureCataloges].CatalogNumber) > If IsNull(varX) Then > ... > Else > ... > End If > End Sub > > > thanks in advance, > Mark > > >

Re: need help writing a DLookup statement "Mark Kubicki" <mark.kubicki[ at ]verizon.net> 12/22/2008 3:23:49 PM thanks... i added them, along with a couple of additional quotes since all of the fields are string, and am now getting this error: 3075 syntax error... the revised code is: varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ "[Option] = 'accent light' and [Manufacturer] = '" & Forms![FixtureCataloges].Manufacturer _ & "' and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber) & "'" "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... [Quoted Text] > Put quotes round your domain as well > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... > > > "Mark Kubicki" wrote: > >> on a form <FixtureCataloges>, >> i have 2 fields that uniquely identify the record: [Manufacturer] and >> [CatalogNumber]; >> >> also on the form is a subform with 1 field: [Options], whose data source >> is >> a table, FixtureCatalogsLuminiareTypes, >> the table has 3 fields [Manufacturer] [CatalogNum] and [Option] >> (-spelled differently... too many people woking on the same...); >> and is related to the data source used for the main form with fields: >> [Manufacturer] and [CatalogNum] >> >> >> when a user goes to the subform, i'd like to check is a value 'accent >> light' >> is present in the subform's data source >> >> >> i've written the following code, but it's not working (i get error '2428' >> invalid domain aggregate...) >> any suggestions would be greatly appreciated >> >> Private Sub Options_GotFocus() >> varX = Null >> Me.Options.Requery >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ >> "[Option] = 'accent light' and [Manufacturer] = " & >> Forms![FixtureCataloges].Manufacturer _ >> & " and [CatalogNum] = " & >> Forms![FixtureCataloges].CatalogNumber) >> If IsNull(varX) Then >> ... >> Else >> ... >> End If >> End Sub >> >> >> thanks in advance, >> Mark >> >> >>

Re: need help writing a DLookup statement RonaldoOneNil 12/22/2008 3:32:01 PM The quotes at the end needs to be inside the last bracket ..... and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & "'") "Mark Kubicki" wrote: [Quoted Text] > thanks... i added them, along with a couple of additional quotes since all > of the fields are string, and am now getting this error: 3075 syntax > error... > > the revised code is: > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ > "[Option] = 'accent light' and [Manufacturer] = '" & > Forms![FixtureCataloges].Manufacturer _ > & "' and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber) > & "'" > > > "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message > news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... > > Put quotes round your domain as well > > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... > > > > > > "Mark Kubicki" wrote: > > > >> on a form <FixtureCataloges>, > >> i have 2 fields that uniquely identify the record: [Manufacturer] and > >> [CatalogNumber]; > >> > >> also on the form is a subform with 1 field: [Options], whose data source > >> is > >> a table, FixtureCatalogsLuminiareTypes, > >> the table has 3 fields [Manufacturer] [CatalogNum] and [Option] > >> (-spelled differently... too many people woking on the same...); > >> and is related to the data source used for the main form with fields: > >> [Manufacturer] and [CatalogNum] > >> > >> > >> when a user goes to the subform, i'd like to check is a value 'accent > >> light' > >> is present in the subform's data source > >> > >> > >> i've written the following code, but it's not working (i get error '2428' > >> invalid domain aggregate...) > >> any suggestions would be greatly appreciated > >> > >> Private Sub Options_GotFocus() > >> varX = Null > >> Me.Options.Requery > >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ > >> "[Option] = 'accent light' and [Manufacturer] = " & > >> Forms![FixtureCataloges].Manufacturer _ > >> & " and [CatalogNum] = " & > >> Forms![FixtureCataloges].CatalogNumber) > >> If IsNull(varX) Then > >> ... > >> Else > >> ... > >> End If > >> End Sub > >> > >> > >> thanks in advance, > >> Mark > >> > >> > >> > > >

Re: need help writing a DLookup statement "Mark Kubicki" <mark.kubicki[ at ]verizon.net> 12/22/2008 3:58:33 PM thanks for your perserverence... (i'm getting all twisted up over a small and what should be very simple statement); now i'm getting this error: 2471, expression produced this error: '[CatalogNum]' - HUH !? -m "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message news:A13368CC-5C5B-4EAE-A09C-691D4A983A34[ at ]microsoft.com... [Quoted Text] > The quotes at the end needs to be inside the last bracket > .... and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & "'") > > > > "Mark Kubicki" wrote: > >> thanks... i added them, along with a couple of additional quotes since >> all >> of the fields are string, and am now getting this error: 3075 syntax >> error... >> >> the revised code is: >> varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ >> "[Option] = 'accent light' and [Manufacturer] = '" & >> Forms![FixtureCataloges].Manufacturer _ >> & "' and [CatalogNum] = '" & >> Forms![FixtureCataloges].CatalogNumber) >> & "'" >> >> >> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in >> message >> news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... >> > Put quotes round your domain as well >> > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... >> > >> > >> > "Mark Kubicki" wrote: >> > >> >> on a form <FixtureCataloges>, >> >> i have 2 fields that uniquely identify the record: [Manufacturer] >> >> and >> >> [CatalogNumber]; >> >> >> >> also on the form is a subform with 1 field: [Options], whose data >> >> source >> >> is >> >> a table, FixtureCatalogsLuminiareTypes, >> >> the table has 3 fields [Manufacturer] [CatalogNum] and [Option] >> >> (-spelled differently... too many people woking on the same...); >> >> and is related to the data source used for the main form with >> >> fields: >> >> [Manufacturer] and [CatalogNum] >> >> >> >> >> >> when a user goes to the subform, i'd like to check is a value 'accent >> >> light' >> >> is present in the subform's data source >> >> >> >> >> >> i've written the following code, but it's not working (i get error >> >> '2428' >> >> invalid domain aggregate...) >> >> any suggestions would be greatly appreciated >> >> >> >> Private Sub Options_GotFocus() >> >> varX = Null >> >> Me.Options.Requery >> >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ >> >> "[Option] = 'accent light' and [Manufacturer] = " & >> >> Forms![FixtureCataloges].Manufacturer _ >> >> & " and [CatalogNum] = " & >> >> Forms![FixtureCataloges].CatalogNumber) >> >> If IsNull(varX) Then >> >> ... >> >> Else >> >> ... >> >> End If >> >> End Sub >> >> >> >> >> >> thanks in advance, >> >> Mark >> >> >> >> >> >> >> >> >>

Re: need help writing a DLookup statement RonaldoOneNil 12/22/2008 4:16:13 PM I can't see anything wrong with the statement. Post your current DLookup statement as it stands "Mark Kubicki" wrote: [Quoted Text] > thanks for your perserverence... (i'm getting all twisted up over a small > and what should be very simple statement); now i'm getting this error: > 2471, expression produced this error: '[CatalogNum]' - HUH !? > > -m > > > "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message > news:A13368CC-5C5B-4EAE-A09C-691D4A983A34[ at ]microsoft.com... > > The quotes at the end needs to be inside the last bracket > > .... and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & "'") > > > > > > > > "Mark Kubicki" wrote: > > > >> thanks... i added them, along with a couple of additional quotes since > >> all > >> of the fields are string, and am now getting this error: 3075 syntax > >> error... > >> > >> the revised code is: > >> varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ > >> "[Option] = 'accent light' and [Manufacturer] = '" & > >> Forms![FixtureCataloges].Manufacturer _ > >> & "' and [CatalogNum] = '" & > >> Forms![FixtureCataloges].CatalogNumber) > >> & "'" > >> > >> > >> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in > >> message > >> news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... > >> > Put quotes round your domain as well > >> > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... > >> > > >> > > >> > "Mark Kubicki" wrote: > >> > > >> >> on a form <FixtureCataloges>, > >> >> i have 2 fields that uniquely identify the record: [Manufacturer] > >> >> and > >> >> [CatalogNumber]; > >> >> > >> >> also on the form is a subform with 1 field: [Options], whose data > >> >> source > >> >> is > >> >> a table, FixtureCatalogsLuminiareTypes, > >> >> the table has 3 fields [Manufacturer] [CatalogNum] and [Option] > >> >> (-spelled differently... too many people woking on the same...); > >> >> and is related to the data source used for the main form with > >> >> fields: > >> >> [Manufacturer] and [CatalogNum] > >> >> > >> >> > >> >> when a user goes to the subform, i'd like to check is a value 'accent > >> >> light' > >> >> is present in the subform's data source > >> >> > >> >> > >> >> i've written the following code, but it's not working (i get error > >> >> '2428' > >> >> invalid domain aggregate...) > >> >> any suggestions would be greatly appreciated > >> >> > >> >> Private Sub Options_GotFocus() > >> >> varX = Null > >> >> Me.Options.Requery > >> >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ > >> >> "[Option] = 'accent light' and [Manufacturer] = " & > >> >> Forms![FixtureCataloges].Manufacturer _ > >> >> & " and [CatalogNum] = " & > >> >> Forms![FixtureCataloges].CatalogNumber) > >> >> If IsNull(varX) Then > >> >> ... > >> >> Else > >> >> ... > >> >> End If > >> >> End Sub > >> >> > >> >> > >> >> thanks in advance, > >> >> Mark > >> >> > >> >> > >> >> > >> > >> > >> > > >

Re: need help writing a DLookup statement "Mark Kubicki" <mark.kubicki[ at ]verizon.net> 12/22/2008 4:27:16 PM Private Sub Options_GotFocus() varX = Null Me.Options.Requery varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ "[Option] = 'accent light' and [Manufacturer] = '" & Forms![FixtureCataloges].Manufacturer _ & "' and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & "'") If IsNull(varX) Then ... ---------------------------------------------- "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message news:7B4EAEC1-E2BB-406E-9311-B46EBDC743C3[ at ]microsoft.com... [Quoted Text] >I can't see anything wrong with the statement. > Post your current DLookup statement as it stands > > "Mark Kubicki" wrote: > >> thanks for your perserverence... (i'm getting all twisted up over a >> small >> and what should be very simple statement); now i'm getting this error: >> 2471, expression produced this error: '[CatalogNum]' - HUH !? >> >> -m >> >> >> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in >> message >> news:A13368CC-5C5B-4EAE-A09C-691D4A983A34[ at ]microsoft.com... >> > The quotes at the end needs to be inside the last bracket >> > .... and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & >> > "'") >> > >> > >> > >> > "Mark Kubicki" wrote: >> > >> >> thanks... i added them, along with a couple of additional quotes since >> >> all >> >> of the fields are string, and am now getting this error: 3075 syntax >> >> error... >> >> >> >> the revised code is: >> >> varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ >> >> "[Option] = 'accent light' and [Manufacturer] = '" & >> >> Forms![FixtureCataloges].Manufacturer _ >> >> & "' and [CatalogNum] = '" & >> >> Forms![FixtureCataloges].CatalogNumber) >> >> & "'" >> >> >> >> >> >> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in >> >> message >> >> news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... >> >> > Put quotes round your domain as well >> >> > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... >> >> > >> >> > >> >> > "Mark Kubicki" wrote: >> >> > >> >> >> on a form <FixtureCataloges>, >> >> >> i have 2 fields that uniquely identify the record: >> >> >> [Manufacturer] >> >> >> and >> >> >> [CatalogNumber]; >> >> >> >> >> >> also on the form is a subform with 1 field: [Options], whose data >> >> >> source >> >> >> is >> >> >> a table, FixtureCatalogsLuminiareTypes, >> >> >> the table has 3 fields [Manufacturer] [CatalogNum] and >> >> >> [Option] >> >> >> (-spelled differently... too many people woking on the same...); >> >> >> and is related to the data source used for the main form with >> >> >> fields: >> >> >> [Manufacturer] and [CatalogNum] >> >> >> >> >> >> >> >> >> when a user goes to the subform, i'd like to check is a value >> >> >> 'accent >> >> >> light' >> >> >> is present in the subform's data source >> >> >> >> >> >> >> >> >> i've written the following code, but it's not working (i get error >> >> >> '2428' >> >> >> invalid domain aggregate...) >> >> >> any suggestions would be greatly appreciated >> >> >> >> >> >> Private Sub Options_GotFocus() >> >> >> varX = Null >> >> >> Me.Options.Requery >> >> >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ >> >> >> "[Option] = 'accent light' and [Manufacturer] = " & >> >> >> Forms![FixtureCataloges].Manufacturer _ >> >> >> & " and [CatalogNum] = " & >> >> >> Forms![FixtureCataloges].CatalogNumber) >> >> >> If IsNull(varX) Then >> >> >> ... >> >> >> Else >> >> >> ... >> >> >> End If >> >> >> End Sub >> >> >> >> >> >> >> >> >> thanks in advance, >> >> >> Mark >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>

Re: need help writing a DLookup statement "Mark Kubicki" <mark.kubicki[ at ]verizon.net> 12/22/2008 6:20:29 PM GOT IT THANKS "Mark Kubicki" <mark.kubicki[ at ]verizon.net> wrote in message news:u1vglIFZJHA.5520[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > Private Sub Options_GotFocus() > varX = Null > Me.Options.Requery > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ > "[Option] = 'accent light' and [Manufacturer] = '" & > Forms![FixtureCataloges].Manufacturer _ > & "' and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber > & "'") > If IsNull(varX) Then > ... > > > ---------------------------------------------- > "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in message > news:7B4EAEC1-E2BB-406E-9311-B46EBDC743C3[ at ]microsoft.com... >>I can't see anything wrong with the statement. >> Post your current DLookup statement as it stands >> >> "Mark Kubicki" wrote: >> >>> thanks for your perserverence... (i'm getting all twisted up over a >>> small >>> and what should be very simple statement); now i'm getting this error: >>> 2471, expression produced this error: '[CatalogNum]' - HUH !? >>> >>> -m >>> >>> >>> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in >>> message >>> news:A13368CC-5C5B-4EAE-A09C-691D4A983A34[ at ]microsoft.com... >>> > The quotes at the end needs to be inside the last bracket >>> > .... and [CatalogNum] = '" & Forms![FixtureCataloges].CatalogNumber & >>> > "'") >>> > >>> > >>> > >>> > "Mark Kubicki" wrote: >>> > >>> >> thanks... i added them, along with a couple of additional quotes >>> >> since >>> >> all >>> >> of the fields are string, and am now getting this error: 3075 syntax >>> >> error... >>> >> >>> >> the revised code is: >>> >> varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", _ >>> >> "[Option] = 'accent light' and [Manufacturer] = '" & >>> >> Forms![FixtureCataloges].Manufacturer _ >>> >> & "' and [CatalogNum] = '" & >>> >> Forms![FixtureCataloges].CatalogNumber) >>> >> & "'" >>> >> >>> >> >>> >> "RonaldoOneNil" <RonaldoOneNil[ at ]discussions.microsoft.com> wrote in >>> >> message >>> >> news:8FDFEE01-0F40-481D-B21F-09ED199A82E8[ at ]microsoft.com... >>> >> > Put quotes round your domain as well >>> >> > varX = DLookup("[Option]", "FixtureCatalogsLuminiareTypes", ..... >>> >> > >>> >> > >>> >> > "Mark Kubicki" wrote: >>> >> > >>> >> >> on a form <FixtureCataloges>, >>> >> >> i have 2 fields that uniquely identify the record: >>> >> >> [Manufacturer] >>> >> >> and >>> >> >> [CatalogNumber]; >>> >> >> >>> >> >> also on the form is a subform with 1 field: [Options], whose data >>> >> >> source >>> >> >> is >>> >> >> a table, FixtureCatalogsLuminiareTypes, >>> >> >> the table has 3 fields [Manufacturer] [CatalogNum] and >>> >> >> [Option] >>> >> >> (-spelled differently... too many people woking on the same...); >>> >> >> and is related to the data source used for the main form with >>> >> >> fields: >>> >> >> [Manufacturer] and [CatalogNum] >>> >> >> >>> >> >> >>> >> >> when a user goes to the subform, i'd like to check is a value >>> >> >> 'accent >>> >> >> light' >>> >> >> is present in the subform's data source >>> >> >> >>> >> >> >>> >> >> i've written the following code, but it's not working (i get error >>> >> >> '2428' >>> >> >> invalid domain aggregate...) >>> >> >> any suggestions would be greatly appreciated >>> >> >> >>> >> >> Private Sub Options_GotFocus() >>> >> >> varX = Null >>> >> >> Me.Options.Requery >>> >> >> varX = DLookup("[Option]", FixtureCatalogsLuminiareTypes, _ >>> >> >> "[Option] = 'accent light' and [Manufacturer] = " & >>> >> >> Forms![FixtureCataloges].Manufacturer _ >>> >> >> & " and [CatalogNum] = " & >>> >> >> Forms![FixtureCataloges].CatalogNumber) >>> >> >> If IsNull(varX) Then >>> >> >> ... >>> >> >> Else >>> >> >> ... >>> >> >> End If >>> >> >> End Sub >>> >> >> >>> >> >> >>> >> >> thanks in advance, >>> >> >> Mark >>> >> >> >>> >> >> >>> >> >> >>> >> >>> >> >>> >> >>> >>> >>> > >