initialize field & lookup wizard ?

[Guest]

Hi there,

I have beend fixing up some db design lately and in doing so have a couple of ?? someone out there might be able to help.
1st question, I was using autonumber to assign a ReceiptNo, but found using a variable and incrementing it using code works better for me (for other funtions of ReceiptNo). Following is what I am using,
NextReceipt = DMax("[ReceiptNo]", "tblReceipts") + 1
I'm not sure how to initialize the 1st receiptno though, if the table is empty (which it will be for start w client) then I get an error when this is used, 'Invalid use of Null'. How do I set the first number for this calculation to use? (Also can it be set to i.e. 1000).

2nd question, more about the db design cleanup (discussed in previous posts - thanks again Steve). I have a table POLine, where a client would choose an item description to order using a combobox which was build based on the lookupwizard in the table POLine for the description field. What is being stored in the description field though is the itemno. I was reffered to an address to view the perils of using the lookup wizard - and I have decided I don't want to go this route, but now I am not sure how to proceed. Meaning- on my POline form, how can I get the user to be able to choose an item (from the item master table) to order. My brain isn't functioning right - because I'm sure this is a query of sorts?
Thanks in advance
Joan

 

[Nikos Yannacopoulos]

Joan,

1st question:
If isnull(DMax("[ReceiptNo]", "tblReceipts")) Then
NextReceipt = 1 '(or 1000 or whatever)
Else
NextReceipt = DMax("[ReceiptNo]", "tblReceipts") + 1
End If
2nd question:
Combo box control source: itemno field in POLine table
Combo box row source: query on your Items table: select itemname first and
then itemno.
Back to your combo box properties: bound column: 2, column count: 2, cloumn
widths: something meaningful (e.g. 5 cm);0 (so the itemno does not actually
display).
This way the user sees item names on the form, but itemnos get stored.

HTH,
Nikos

Hi there,

I have beend fixing up some db design lately and in doing so have a couple

of ?? someone out there might be able to help.

1st question, I was using autonumber to assign a ReceiptNo, but found

using a variable and incrementing it using code works better for me (for
other funtions of ReceiptNo). Following is what I am using,

NextReceipt = DMax("[ReceiptNo]", "tblReceipts") + 1
I'm not sure how to initialize the 1st receiptno though, if the table is

empty (which it will be for start w client) then I get an error when this is
used, 'Invalid use of Null'. How do I set the first number for this
calculation to use? (Also can it be set to i.e. 1000).

2nd question, more about the db design cleanup (discussed in previous

posts - thanks again Steve). I have a table POLine, where a client would
choose an item description to order using a combobox which was build based
on the lookupwizard in the table POLine for the description field. What is
being stored in the description field though is the itemno. I was reffered
to an address to view the perils of using the lookup wizard - and I have
decided I don't want to go this route, but now I am not sure how to proceed.
Meaning- on my POline form, how can I get the user to be able to choose an
item (from the item master table) to order. My brain isn't functioning
right - because I'm sure this is a query of sorts?

 

[John Vinson]

Hi there,

I have beend fixing up some db design lately and in doing so have a couple of ?? someone out there might be able to help.
1st question, I was using autonumber to assign a ReceiptNo, but found using a variable and incrementing it using code works better for me (for other funtions of ReceiptNo). Following is what I am using,
NextReceipt = DMax("[ReceiptNo]", "tblReceipts") + 1
I'm not sure how to initialize the 1st receiptno though, if the table is empty (which it will be for start w client) then I get an error when this is used, 'Invalid use of Null'. How do I set the first number for this calculation to use? (Also can it be set to i.e. 1000).

NextReceipt = NZ(DMax("[ReceiptNo]", "tblReceipts")) + 1

will do this (or you could just manually enter the very first receipt
number). The NZ() trick is still useful if you've got a two part ID,
for instance if you want the number to start over anew every year.

2nd question, more about the db design cleanup (discussed in previous posts - thanks again Steve). I have a table POLine, where a client would choose an item description to order using a combobox which was build based on the lookupwizard in the table POLine for the description field. What is being stored in the description field though is the itemno. I was reffered to an address to view the perils of using the lookup wizard - and I have decided I don't want to go this route, but now I am not sure how to proceed. Meaning- on my POline form, how can I get the user to be able to choose an item (from the item master table) to order. My brain isn't functioning right - because I'm sure this is a query of sorts?
Thanks in advance
Joan

Simply put a Combo Box on the Form. You don't have to have a combo box
in the table in order to do this! Base the Combo Box on the lookup
table (whatever that is); use the ID number as the bound column of the
combo but set its width to 0 in the ColumnWidths property. The form
toolbox combo wizard will set it up this way for you automatically.