Form issues.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Feeling very stoopid today; help would be appreciated.

OK, I've a form with inventory information. What I'm trying to do is code a
loop that allows the user to just enter a certain amount of items, then
preforms that many iterations of the loop which writes a new record into a
table. The code works fine except for two issues:

1) One of the tables to which I'm writing uses an autonumber as a PK. I'm
not sure how to circumvent this; do I have to query the table to find the
highest number, increment that, then use that as the inserted value for that
field?

2) This is slightly embarrassing as this is about scope and I know I should
be able to dope this out. Anyway, at the click event of the "Add Record"
button, I've asked the system to capture the value of several text boxes.
However, I got a message indicating that the information from each text box
was unavailable as the focus was not there.

Even though I didn't think it would work, I jsut tried to stick the variable
declarations and assingments in the general declarations section, which gave
me a message indicating what I was trying to do was an "invalid outside
procedure".

So, do I have to, for each text value I want to capture, code a "Lost Focus"
procedure that captures that value and holds it, or within the code of my Add
Record button, do I have to programmatically send the focus to each text box,
then capture the value, or is there a more sensible way to capture all the
data in one fell swoop?

Thanks as always.
 
pvdalen said:
Feeling very stoopid today; help would be appreciated.

OK, I've a form with inventory information. What I'm trying to do is code a
loop that allows the user to just enter a certain amount of items, then
preforms that many iterations of the loop which writes a new record into a
table. The code works fine except for two issues:

1) One of the tables to which I'm writing uses an autonumber as a PK. I'm
not sure how to circumvent this; do I have to query the table to find the
highest number, increment that, then use that as the inserted value for that
field?

2) This is slightly embarrassing as this is about scope and I know I should
be able to dope this out. Anyway, at the click event of the "Add Record"
button, I've asked the system to capture the value of several text boxes.
However, I got a message indicating that the information from each text box
was unavailable as the focus was not there.

Even though I didn't think it would work, I jsut tried to stick the variable
declarations and assingments in the general declarations section, which gave
me a message indicating what I was trying to do was an "invalid outside
procedure".

So, do I have to, for each text value I want to capture, code a "Lost Focus"
procedure that captures that value and holds it, or within the code of my Add
Record button, do I have to programmatically send the focus to each text box,
then capture the value, or is there a more sensible way to capture all the
data in one fell swoop?
Click to expand...


I don't understand what you're trying to do in 1). You say
you are writing a bunch of records to a table, then you say
something about "one of the tables". Whatever, I strongly
suspect that adding a bunch of identical records is
indicative of a normalization problem somewhere.

As for 2), I can't be sure since you didn't post any of your
code, but it sounds like you're stuck in VB land. ;-)
In Access VBA, you do not use a control's Text property to
retrieve its value, you use its Value property with no
worries about the focus.
 
Hey Marshall,

Thanks for the response!

As for 1), well, I don't think it's a normalization issue as the records I'm
adding are going to have different serial numbers, which the code generates
per iteration. As to why then the autonumber is needed, I'll try to explain,
realizing I might be wrong :)

Customers can have a thingie or a widget (one or more) or both. So, I made
a Customer, Thingie and Widget table. The latter two have fields in common,
such as order date, etc, so I split that off into a separate Inventory table.
It is this inventory table to which I'm writing that I'm having the issue.
As a given row can contain either a widget or a thingie, those values can't
be used for a PK, so I used an autonumber. Should I just get rid of
Inventory and have those fields be present in Thingie and Widget? I thought
a lot about that, but I thought that would just be lazy or bad design to have
those fields repeated in different tables.

And as for 2), well, crap. I think I knew that some time ago, but just
forgot over time (it's been a while). Thanks for the reminder, and for
everything else.
 
OK, that helps clarify things. Even though I still don't
understand why Widgets and Thingies need a different table,
that's probably neither here nor there wrt your question. I
think you question boils down to writing a bunch of records
to the inventory table, capturing their autonumber value and
writing that to the widget or thingie table, right?

If so, then how about using code to open recordsets on all
three tables. Then use a loop to add a record to inventory
and another to either thingie or widget. The code could
look something like this OTTOMH air code:

DIm rsInv As Recordset
Dim rsItemType(1) As Recordset
Set rsInv = db.OpenRecordset("Inventory", dbOpenDynaset)
Set rsItemType(0) = db.OpenRecordset("Widget")
Set rsItemType(1) = db.OpenRecordset("Thingie")

For lngSerial = xxxx to yyyy
With rsInv
.AddNew
!SerialNo = lngSerial
!Itemtype = intItemType
. . .
lngKey = !autonumberfield
.Update
End With
With rsItemType(intItemType)
.AddNew
!InvKey = lngKey
!otherfield = something
. . .
.Update
End With
Next lngSerial

I don't know if it's possible to keep the code independent
of the products. This is the normalization issue with
having different tables for different products. Every time
you add a new product, you have to add more code, which is
indicative of a weak design. However, if the products have
major differences in their attributes, you may decide to
continue going down the road you're already on. Or you can
try to find a way to further normalize the data by having an
Attributes table that would be able to deal with any
product, but event then each product may need it's own
code??
 
Hey again Marshall,

The reason I need two separate tables is that a customer can have a thingie,
or a widget, or both, or one thingie and multiple widgets. But they do have
some properties in common, like order date, ordered by, cost at time of
order, etc. which is why I made that Inventory table. Since the Inventory
table has a field that acts as a FK to Thingie and another field that acts as
a FK to Widget, and in a given row only one of those fields will be
populated, I figured having an autonumber PK would be the way to go.

So, sorry if I wasn't clear earlier; I can programmatically fill the Thingie
and Widget tables just fine, but doing so for the Inventory table failes
because of the autonumber field. So, do you think I should just open a
recordset to retrieve the largest autonumber value already used? Or should I
just scrap the Inventory table altogether and let the Thingie and Widget
tables hold common fields like the order information? Doing so wouldn't
screw the normalization rules, because each thingie or widget is going to
have that information per record; like I said earlier, I just added the
Inventory table because I thought it would be better design than having two
tables with field names in common.

Another consideration is whether or not I'm missing a basic functionality
that allows me to write to an autonumber field programmatically?

Thanks again.


Marshall Barton said:
OK, that helps clarify things. Even though I still don't
understand why Widgets and Thingies need a different table,
that's probably neither here nor there wrt your question. I
think you question boils down to writing a bunch of records
to the inventory table, capturing their autonumber value and
writing that to the widget or thingie table, right?

If so, then how about using code to open recordsets on all
three tables. Then use a loop to add a record to inventory
and another to either thingie or widget. The code could
look something like this OTTOMH air code:

DIm rsInv As Recordset
Dim rsItemType(1) As Recordset
Set rsInv = db.OpenRecordset("Inventory", dbOpenDynaset)
Set rsItemType(0) = db.OpenRecordset("Widget")
Set rsItemType(1) = db.OpenRecordset("Thingie")

For lngSerial = xxxx to yyyy
With rsInv
.AddNew
!SerialNo = lngSerial
!Itemtype = intItemType
. . .
lngKey = !autonumberfield
.Update
End With
With rsItemType(intItemType)
.AddNew
!InvKey = lngKey
!otherfield = something
. . .
.Update
End With
Next lngSerial

I don't know if it's possible to keep the code independent
of the products. This is the normalization issue with
having different tables for different products. Every time
you add a new product, you have to add more code, which is
indicative of a weak design. However, if the products have
major differences in their attributes, you may decide to
continue going down the road you're already on. Or you can
try to find a way to further normalize the data by having an
Attributes table that would be able to deal with any
product, but event then each product may need it's own
code??
--
Marsh
MVP [MS Access]

As for 1), well, I don't think it's a normalization issue as the records I'm
adding are going to have different serial numbers, which the code generates
per iteration. As to why then the autonumber is needed, I'll try to explain,
realizing I might be wrong :)

Customers can have a thingie or a widget (one or more) or both. So, I made
a Customer, Thingie and Widget table. The latter two have fields in common,
such as order date, etc, so I split that off into a separate Inventory table.
It is this inventory table to which I'm writing that I'm having the issue.
As a given row can contain either a widget or a thingie, those values can't
be used for a PK, so I used an autonumber. Should I just get rid of
Inventory and have those fields be present in Thingie and Widget? I thought
a lot about that, but I thought that would just be lazy or bad design to have
those fields repeated in different tables.
Click to expand...
Click to expand...
 
I still don't think your design is fully normalized. It's
not quite set in concrete, but a general guideline is to ask
the question - If I ever change the value of something, can
I do it by changing a single field in a single record in a
single table and no code? That can be vaguely extended to
adding a new item to a table without adding a new field,
table or any code. But I guess that's still neither here
nor there for your question.

If you look closely at the code I posted, you'll see that I
demonstrated a way to retrieve the new record's AutoNumber
value as it's being added. Then, you can use that as the
foreign key to add a new record to the other table.

The only way I know of adding a new record with a
predetermined AutoNumber primary key value is to use an
Insert Into SQL statement to append the record, but I don't
think you have any need for this kind of thing.
 
Hey Marsh,

Sorry I didn't get back before now; parents were in from hometown. :)

Anyway, what you suggested did work. Thanks a bundle!

Feel free to call yourself "Big Tiime"; I'll back you up.

Incidentally, regarding the normalization you'd mentioned, I did try to
employ the thought that a change should affect one value in oe table, and I
do think that's what I have. If you have the time or inclination to take
this off-line, feel free to e-mail me at (e-mail address removed).

Thanks again,
Paul

Marshall Barton said:
I still don't think your design is fully normalized. It's
not quite set in concrete, but a general guideline is to ask
the question - If I ever change the value of something, can
I do it by changing a single field in a single record in a
single table and no code? That can be vaguely extended to
adding a new item to a table without adding a new field,
table or any code. But I guess that's still neither here
nor there for your question.

If you look closely at the code I posted, you'll see that I
demonstrated a way to retrieve the new record's AutoNumber
value as it's being added. Then, you can use that as the
foreign key to add a new record to the other table.

The only way I know of adding a new record with a
predetermined AutoNumber primary key value is to use an
Insert Into SQL statement to append the record, but I don't
think you have any need for this kind of thing.
--
Marsh
MVP [MS Access]


The reason I need two separate tables is that a customer can have a thingie,
or a widget, or both, or one thingie and multiple widgets. But they do have
some properties in common, like order date, ordered by, cost at time of
order, etc. which is why I made that Inventory table. Since the Inventory
table has a field that acts as a FK to Thingie and another field that acts as
a FK to Widget, and in a given row only one of those fields will be
populated, I figured having an autonumber PK would be the way to go.

So, sorry if I wasn't clear earlier; I can programmatically fill the Thingie
and Widget tables just fine, but doing so for the Inventory table failes
because of the autonumber field. So, do you think I should just open a
recordset to retrieve the largest autonumber value already used? Or should I
just scrap the Inventory table altogether and let the Thingie and Widget
tables hold common fields like the order information? Doing so wouldn't
screw the normalization rules, because each thingie or widget is going to
have that information per record; like I said earlier, I just added the
Inventory table because I thought it would be better design than having two
tables with field names in common.

Another consideration is whether or not I'm missing a basic functionality
that allows me to write to an autonumber field programmatically?

Thanks again.
Click to expand...
Click to expand...
 
Back
Top