MS Access Small VB problem

[joecosmides]

I have a form in datasheet view which has a part number field,
quantity field, and a description field. The following code allows me
to choose a partn number from the part number field and when I choose
it, it will populate the proper descriptionin the description field as
well.

Private Sub ItemNumber_AfterUpdate()
Dim XDescription As String
Dim XPrice As String

XDescription = [ItemNumber].[Column](1)
XPrice = [ItemNumber].[Column](2)
Description = XDescription
Price = XPrice
End Sub


My problem is that when I need to add a part number that is not
already on the list. It's not on the list because it will only be used
once in probably several months so it's not worth putting in the list.
When I add the part number myself and then tab over to the quantity
field or description field I get a debug error. When I debug it, it
highlights XDescription = [ItemNumber].[Column](1). I guess it's doing
that because it cannot match any description to the bogus part number
I put in. Is there a way that I can achieve this?

Thanks a million.

 

[Beetle]

If this "bogus" part number is always the same number, then i see no
reason not to add it to the list, along with a generic description and a
price.

If it's always going to be a different number, and it won't really
have a description/price, then you you may be able to get it to work
by modifying your code like;

Private Sub ItemNumber_AfterUpdate()
Dim XDescription As String
Dim XPrice As String

XDescription = nz([ItemNumber].[Column](1),"")
XPrice = nz([ItemNumber].[Column](2),0)
Description = XDescription
Price = XPrice
End Sub

BTW - you really don't need to declare string variables in your code. You
could just do;

Private Sub ItemNumber_AfterUpdate()

Me![Description] = Me![ItemNumber].[Column](1)
Me![Price] = Me![ItemNumber].[Column](2)

End Sub

Not a big deal, but it's a little less coding.

 

[joecosmides]

If this "bogus" part number is always the same number, then i see no
reason not to add it to the list, along with a generic description and a
price.

If it's always going to be a different number, and it won't really
have a description/price, then you you may be able to get it to work
by modifying your code like;

Private Sub ItemNumber_AfterUpdate()
Dim XDescription As String
    Dim XPrice As String

    XDescription = nz([ItemNumber].[Column](1),"")
    XPrice = nz([ItemNumber].[Column](2),0)
    Description = XDescription
    Price = XPrice
End Sub

BTW - you really don't need to declare string variables in your code. You
could just do;

Private Sub ItemNumber_AfterUpdate()

    Me![Description] = Me![ItemNumber].[Column](1)
    Me![Price] = Me![ItemNumber].[Column](2)

End Sub

Not a big deal, but it's a little less coding.

--
_________

Sean Bailey

I have a form in datasheet view which has a part number field,
quantity field, and a description field. The following code allows me
to choose a partn number from the part number field and when I choose
it, it will populate the proper descriptionin the description field as
well.

Private Sub ItemNumber_AfterUpdate()
Dim XDescription As String
    Dim XPrice As String

    XDescription = [ItemNumber].[Column](1)
    XPrice = [ItemNumber].[Column](2)
    Description = XDescription
    Price = XPrice
End Sub

My problem is that when I need to add a part number that is not
already on the list. It's not on the list because it will only be used
once in probably several months so it's not worth putting in the list.
When I add the part number myself and then tab over to the quantity
field or description field I get a debug error. When I debug it, it
highlights XDescription = [ItemNumber].[Column](1). I guess it's doing
that because it cannot match any description to the bogus part number
I put in. Is there a way that I can achieve this?

Thanks a million.- Hide quoted text -

- Show quoted text -

Thank you so much. I know very little about VB and that worked great.