Hi All,
About a week ago, I got some advice here, part of which still confuses me. I was (am) trying to get my query to take the value of a control on my form, and use it as one of the criteria in my query (That part works great thanks to charityg!). What I need to do is to move a quantity of a part number from one location in a warehouse to another. If the new location already has a quantity of the same part number in it, then the quantity being moved out of the original location will be added to the existing quantity in the new location. If there is no matching part number in the new location, then a record will be added to the table putting it there. Simple enough. The new location is keyed into a combo box by the user, where I want the associated query to query by both the PN(automatically fetched) and location(keyed in by the user). It was suggested that I use the following code with my query criteria to determine if the query will return any records. The code I can’t get to work is as follows:
IF DCount("[PartNumber]", "[All Fields Table (A)]", _
"[PartNumber] = '" & forms!frmRelocateTestFix!UBPartNumber & "' AND [Location] = '*'")>0 then
MsgBox "got one" else
MsgBox "Zero quantity"
endif
The criteria for Part Number in the query that gets its data from another control is:
[Forms]![frmRelocateTestFix]![APart Number] ‘works great’
How do I use the above ‘If Dcount’ code with my query? Or do I? I have put it under the Part Number criteria in the ‘or’ space just below, and I get an error upon saving it that says: “The expression you have entered contains invalid syntax. You may have entered an operand without and operator”. I’m sure that I’m doing many, many things wrong. Can you help?
Thanks in advance, Chuck
About a week ago, I got some advice here, part of which still confuses me. I was (am) trying to get my query to take the value of a control on my form, and use it as one of the criteria in my query (That part works great thanks to charityg!). What I need to do is to move a quantity of a part number from one location in a warehouse to another. If the new location already has a quantity of the same part number in it, then the quantity being moved out of the original location will be added to the existing quantity in the new location. If there is no matching part number in the new location, then a record will be added to the table putting it there. Simple enough. The new location is keyed into a combo box by the user, where I want the associated query to query by both the PN(automatically fetched) and location(keyed in by the user). It was suggested that I use the following code with my query criteria to determine if the query will return any records. The code I can’t get to work is as follows:
IF DCount("[PartNumber]", "[All Fields Table (A)]", _
"[PartNumber] = '" & forms!frmRelocateTestFix!UBPartNumber & "' AND [Location] = '*'")>0 then
MsgBox "got one" else
MsgBox "Zero quantity"
endif
The criteria for Part Number in the query that gets its data from another control is:
[Forms]![frmRelocateTestFix]![APart Number] ‘works great’
How do I use the above ‘If Dcount’ code with my query? Or do I? I have put it under the Part Number criteria in the ‘or’ space just below, and I get an error upon saving it that says: “The expression you have entered contains invalid syntax. You may have entered an operand without and operator”. I’m sure that I’m doing many, many things wrong. Can you help?
Thanks in advance, Chuck
