Form Question? Correction

[G Lam]

In my previous post, the name of the field in the Order Input form should be
OrdPartNbr.

Hi, I have a form to add data on the fly. When user enters the part# in a
field of order input form, the afterupdate event will check if the part#
exists in the part# table. If it is not, a AddPart form will pop up and asks
the user to input the new part data.
I want the part# entered in the order input form carry to the Addpart form,
but unable to do that.
In the Order Input form!OrdPartnbr afterupdate event, I put a line of code
like
this:
if dlookup return null then:
DoCmd.OpenForm "FrmAddPart", acNormal, , "PartNbr = """ & L & """",
acFormAdd, acDialog
L is a string variable that equals the part# entered by the user in the
order input form.
The FrmAddPart's data source is a query "qryAddPart" from the tblParts,
which has on two fields - PartNbr, Description.
I also tried
DoCmd.OpenForm "FrmAddPart", acNormal, , "PartNbr = """ & L & """", ,
acDialog
DoCmd.OpenForm "FrmAddPart", acNormal, , "PartNbr = " & L , , acDialog
DoCmd.OpenForm "FrmAddPart", acNormal, , "PartNbr = " & L , acFormAdd,
acDialog
or add another line:
frmAddPart!PartNbr = L
or
forms!frmAddPart!PartNbr = l
but none of them worked.
I checked in the immediate window, the L did has the part# value.
How Can I get it right?
Thank you.
Gary

 

[Chris]

I think your problem is that you are using literal
quotation marks, which VB instead interprets as the end of
the string. The command instead could be

DoCmd.OpenForm "FrmAddPart", , , "PartNbr = " & Chr(34) & L
& Chr(34)

Where Chr(34) is the quotation mark.

Try and see how far you get with that

Cheers

Chris

btw. if anyone has a beter idea for inserting "s into a
string than Chr(34), I'd love toi hear it. String look so
complicated with that in it

 

[Tim Ferguson]

btw. if anyone has a beter idea for inserting "s into a
string than Chr(34), I'd love toi hear it. String look so
complicated with that in it

You can either double up the double quotes:

"PartNbr = """ & L & """"

or use single ones

"PartNbr = '" & L & "'"


If there is any chance of L containing single quotes, then the first one is
preferable; if there is any chance of L containing double quotes, then the
second one is better; and if L could contain either or both, then you need
to manage them with custom function. Consider these:

WHERE BookTitle = "A man called ""Horse""."
OR BookTitle = 'A man called "Horse".'

AND AuthorName = "Brian ""the pen"" O'Hare"
OR AutherName = 'Brian "the pen" O''Hare'



PS in any case, if PartNbr is a number, there shouldn't be any quotes at
all should there?

"PartNbr = " & CStr(L)

HTH


Tim F

 

[G Lam]

Chris and Tim,
The PartNbr field is a string field. I tried both suggestions, but still
failed. I even tried hard code a string :
DoCmd.OpenForm "FrmAddPart", acNormal, ,"PartNbr = ' NewPart ' " , ,
acDialog
but won't work either. That's strange.
I ended up using Glenn's suggestion - Openargs. It worked.
I am still wondering what went wrong with the wherecondition argument. I
hope some body can have an answer.
Thank you all.
Gary

 

[Glenn]

Gary,

The where condition is searching for a matching part# to
load those details in the form. You are adding a new Part#
therefore until you create the details there is no match.
Hope that is clear,
Glenn.

 

[G Lam]

Glenn,
Oop! you are right. I was adding a new record, not searching for records in
the table.
Thank all you guys.
Gary

Gary,

The where condition is searching for a matching part# to
load those details in the form. You are adding a new Part#
therefore until you create the details there is no match.
Hope that is clear,
Glenn.

 

[Tim Ferguson]

Oop! you are right. I was adding a new record, not searching for
records in the table.

I usually create the new record first in the calling code and then focus
the new form on it. This way I can pick up any errors (e.g. key violations,
validation rule errors) before opening a blank form and suprising the user.

All the best


Tim F