Problem: combobox, table lookup, adding new record

[Rob]

I have a form for entering "Items" from receipts. Each Item has a
name, but it also has one or more Short Names -- the names that are
actually written on the receipt. Here is my problem: I have a nice
continuous form for entering new items right now, but I want to be able
to add a short name to an item (since it may have many--the same item
may have different short names at different stores) on the same form as
the rest of the items properties.

This is what I was thinking: a Combo Box that contains all the short
names currently assigned to the current item, with the ability to write
in an item not in the combobox list, and have it be added to the "short
names" table for the current item.

I am having trouble figuring out what my control source, row source,
bound column, etc, should be in this case. Here is the setup:

Item Table
-ID
-Name
-Description
-etc

ItemShortNames Table
-ID
-ItemID
-Short Name

Again, I want my combobox to contain a list of all the different short
names assigned to the particular item, inside a continuous form (for
easy entry and editing of items--so I don't think subforms will work)
with the ability to type in a new short name in the combobox at will.

Please help. I've been working on this problem for a long time and I
can't figure it out...it's really slowing me down.

Thanks

Rob

 

[Jeff Boyce]

Rob

If you are trying to use a combo box and do NOT wish to limit entries to
what is in the list, check the NotInList event in Access HELP.

Note, however, that combo boxes are great for allowing selection of an item
from a list of possibles. You may want to consider some other approach to
showing all the items from one table that are associated with a record in
another table. Usually, a continuous subform does this well.

 

[Eric D via AccessMonster.com]

Rob,

As I just moved and have purchased many things for the new house, I'm
deducting state sales tax this year and think I've done something similiar to
what you are doing.

My form prompts for the date, then the store (combo box) where I purchased
the item. After selecting the store, my next field will display criteria
(your short names) based on the store entered. If you don't have a table that
identifies the stores you shop at, you might have to modify your table layout
to address this.

The biggest issue is being able to add new items to the short name table "on
the fly".

If you use a combo box to list the short names, you can pull the ItemID from
that table, just hide it when displaying the data in the combo box. In the
OnExit of this combo box, check what was entered. If not listed in the short
name table, my code would prompt me to add or skip. If I say add, my code
would add the short name, along with the ItemID (or whatever field that links
the data to the other table), to the appropriate table. You would probably
have to code for null, etc., here.


I've done this to a project at work also. I select the equipment TYPE I want
to work with... then the next combo box displays the valid machines in that
TYPE group. The user makes a selection and a form is displayed for user input
that is based on the equipment selected. No auto-add here though. Trying to
avoid user " awshucks ".

 

[Rob]

Jeff: Thanks for the tip. I would be glad to use a subform, but the
form it would be on is continuous, and Access tells me I cant use a
subform on a continuous form.

I would like to keep the form continuous because it is essentially a
data entry form, and I have been able to fit all the required entries
on one line, thus making it almost a grid.

Eric D: Yes, I have tables and relationships setup for both stores and
store locations, but this has nothing do to specifically with my
problem (I am assigning 1 or more short names directly to each item
directly, independent of store)

Could you help me out with writing the code required for this?

So here is the idea then: I would have a combobox, control source on
the itemID, bounded to an invisible column, and the second column being
the short name. Then, the user could write in the combobox, if
desired, and if what is written is not in the list (wouldn't it compare
what I wrote with the first (invisible) column? If so, wouldn't it
always not be in the list?) it should add it to the list--not just
this, but also add an entry to the ItemShortName table, containing a
new ID for this new short name (an autonumber), the itemID, and the
short name itself.

Sound like what you are suggesting? The code that really worries me is
the last part: writing to the database a new record. How would I do
something like this?

Thanks for the speedy help

Rob

 

[Eric D via AccessMonster.com]

What did I miss?
quote " (since it may have many--the same item
may have different short names at different stores)
end of quote

How would you identify which short name is associated with which store?
Now you say short names are independent of stores...?

I could help you out with the code, but you need to send me screen shots of
your forms and tables or a copy of the db. I'll see if I can set up a temp
email in yahoo so that we can communicate directly.

 

[Rob]

while it's true that short names are associated with stores, I dont
think I need to implement such a connection in the database, since it
is entirely likely that two stores could have the same short name for
one particular product, and as well that one store could have the name
different (however slightly) on two different cash registers. So I
figured I would just give the item short names. Then, as I enter the
data off the receipt, I type in the name the receipt has into a
combobox containing all the short names, and out pops the actual item.

RE the code, I am an accomplished programmer, so just the main calls
would be enough to get me started.

 

[Brian Bastl]

Simple solution:
your current combo should stay bound, but hidden on new record. Add an
unbound combo for your Short Names, positioning it directly on top of your
Items combo. The following, although quite crude, should give you an idea.

cboShortNames: 'unbound
Rowsource = SELECT ID, ItemID, ShortName FROM tblItemShortNames
Columns = 3
Column Widths = 0";0";1.5
Bound Column = 1

--------------------------------------
'Your main form to input Receipts. add your own error handling

Private Sub Form_Current()
If Me.NewRecord Then
With Me.cbShortNames
.Visible = True
.TabStop = True
.TabIndex = 1
End With

With Me.ItemID
.Visible = False
.TabStop = False
End With
Else
With Me.cbShortNames
.Visible = False
.TabStop = False
End With

With Me.ItemID
.Visible = True
.TabStop = True
.TabIndex = 1
End With
End If
End Sub
------------------------------------------
Private Sub cbShortNames_NotInList(NewData As String, Response As Integer)

If MsgBox(NewData & " doesn't exist." & vbCrLf & vbCrLf & _
"Do you want to add it?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmShortNames", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

End Sub
-------------------------------------------
Private Sub cbShortNames_AfterUpdate()
Me.ItemID = Me.cbShortNames.Column(1)
Me.ItemQty.SetFocus 'move to next control
Me.cbShortNames.Visible = False
Me.ItemID.Requery
Me.ItemID.Visible = True
End Sub


Is this what you're after?

Brian