Need to create multi-table form with "update or insert" logic

[geolemon]

New development effort here, seeking advice on the best way to pull it off. I'm more familiar with SQL Server and DB2, but this is perfect app to get me some experience with Access:

In this, I have created an "inventory" table with relationships to several other [dimension type] tables... "location", "status", "job", etc.
For the purpose of this question, I'll just use the location table as my example.

I want to create a nice user-friendly form (or switchboard?) for our inventory manager to use [which will mask the fact that there are multiple tables], in this way:

He will be prompted to input a part number.
If the part number exists, the part data and related dimension data will be returned to him in a single form.
If it doesn't exist - the same form presented to him, with only the part number box filled in.
In both cases, drop-down boxes will give him his options, as queried from the dimension tables.

The complexity is this:

I'd like to create each drop-down box, to allow the manager to select an existing value from the dimension table when appropriate.
However, I'd like him to also be able to type in a new value into this box if he wishes - for example, a new bin location, for a new job (in the "location" table) - creating a new record in that corresponding dimension table. Maybe a new bin location, maybe a new job number.

Pardon me for any semantics or inaccuracies with respect to Access-specific terminology... as you read this please bear in mind I am speaking at a conceptual level seeking to *learn* the application level.
Again, please bear in mind that I am not a new DBA, but I am completely new to Access.

I'm seeking advice on how to pull this off - possible within an Access environment? I'm not unfamiliar with VBA, but still non-expert. Most of my VBA experience is with Excel. I expect because there's some "if/else" logic implied in this that I'll need to tie some VBA procedural type code to my form somehow?

Thanks in advance for advice or further questions!

 

[PHV]

Look at the NotInList event of the ComboBox object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[geolemon]

I have a feeling that detail is several layers beyond where I am at... and/or Access can't create a multi-table form?

A combo box, in my experimentation so far, doesn't appear to be a control that I can use (doesn't seem to do what I want):
What I want to do essentially is create an updatable "view" (to use DB2 terminology), involving a multi-table join.

When I add a combo box, I have the ability to pull data only from a single table (unless I missed something), and pull multiple columns in for reference in the drop down.
That sounds ideal, at face value!
However, when I actually *select* a row from that drop down, only one column value appears - the rest of the record is no longer displayed. Is this intended just to be a helpful "so I'm sure I recognize the row" tool for an end user?

I haven't gotten so far as to try to update table with this form... but I didn't see any option to control the update vs. insert logic (I assume it's basic primary key logic), or to prompt the user maybe with a nice confirmation "the record doesn't exist, would you like to add this record to your database?"... just has me wondering if this really will do what I want, or if this "form" concept is really single-table, no-join basic stuff that's a little past my concept for denormalizing my tables to create a "one stop shop" type of form for my end users?

Thanks again for any help...
Definitely appreciate any explicit help, I think I may need to adjust my thinking to this "everything built-in" world of Access... but that means figuring out the "how" in this world too - so thanks for places like this!

 

[fneily]

Probably help if you posted your tables structures - fields and primary key.
For muliple tables, you'd create a query. The tables would be joined in the query and this query would be the source for the form. You must be careful with the primary keys or the query will not be updatable.
Using a combobox for a record search is common and if you search the Access forums you'll find alot of examples. It's also in basic Access books. Example with a combobox using a text search:
Private Sub shaid_AfterUpdate()
Dim SQLText
SQLText = "Select * From [tblParcel] Where [Itemnumber] = " & Chr(34) & Me![shaid] & Chr(34)

Again, most of what you're asking is in Access books. For more specific answers, post table structures and the code you have so far.

By the way, your tables should be normalized.
Forms![MainREAGform3].RecordSource = SQLText
Me![shaid] = Null
End Sub

Some code for checking a recordUser selects from a dropdown, sees if the record exists, if not asks user if they want to add it to the table)
Dim R As DAO.Recordset, RS As DAO.Recordset
Dim UserSelection
Set R = CurrentDb.OpenRecordset("Select * From [tblParcel] Where [Itemnumber] = " & Chr(34) & Me![shaid] & Chr(34))
If R.RecordCount = 0 Then
UserSelection = MsgBox("SHA Number Does Not Exit" & Chr(10) & "Add To Missing SHA Table?", vbYesNo)
Select Case UserSelection
Case 6
Set db = CurrentDb()
Set RS = db.OpenRecordset("Missing_SHA_Table", dbOpenDynaset)
RS.AddNew
RS![MMCNumber] = Forms![MMC_And_SHA_Update_Form]![MMCNumber]
RS![SHANumber] = Me![shaid]
RS![DateChecked] = Now()
RS![Reagtable] = Reaghold
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
Case 7
End Select
Else
blah, blah

 

[geolemon]

Thanks, that's beautiful info!

Bear in mind, *all* information I'm sure lives in Access books - in discrete form.

To contrast, the question that I'm asking about is a composite application:
1)creating a form 2)using a stored query (thanks for that piece) to handle multiple tables 4) using a form that can handle "if not available to update, then accept input for insert" [which 5) could be handled 2 different ways - single form vs. input decides which form to present].

I know it may seem simple when you have an awareness of where "access handles this automatically" ends and "programmers must manage it" begins (and all the "access has this proprietary built-in feature" in between)...
...in my case I'm admittedly a bit paranoid, coming from DB2 and SQL Server environments where much, much more is left to the DBA. My apologies - I fear THOSE "givens" (well known to Access-specific users), and don't want to stub my toe because I didn't know that element was a given, an automatic, or "coulda been done easier" because of a proprietary function that "everyone knows about". ;-)

So - thank you for the explicit answer!

 

[fneily]

Yeah, it would be like me asking you DB2 questions which I know nothing about.
However, it would still help us if we could see what you have actually developed in ways of tables and code. I'm sure you can appreciate that.

 

[geolemon]

My table structure is pretty simple - these are pretty preliminary at this point, I may have a column or two more in a few as I review requirements.

The Inventory table is a central table. The remainder are dimension-style tables joined to the Inventory table by their primary keys (to matching foreign keys in the Inventory table):

Inventory:
[PK] custID
[PK] mfgPN
[PK] locationID
[PK] statusID
[PK] packageID
qty

Customer:
[PK] custID
customerName
discount

Parts:
[PK] mfgPN
manufacturer
description

PartsLocation:
[PK] locationID
facility
bin

PartsStatus:
[PK] statusID
status

PartsPackaging:
[PK] packageID
packageType
SMTCompatible

Pretty straightforward - I'm desiring a single form to allow the inventory manager to add parts to inventory, and thereafter increment and decrement inventory quantities, set and change parts statuses, move parts around facilities - fairly simple application to allow someone to pull a query to locate how many of a part lives at what location that can be used for a particular job (parts availability).

Thanks for the info - I believe I've got enough info to be dangerous, I'm hoping to have some time to work on this through the weekend [Stupidly, I've volunteered to manage the data input in the short term, and killed any sense of urgency. Take note, don't make this mistake yourself! lol]

Thanks again for any more suggestions, or changes to suggestions!

 

[fneily]

If you open up Access, cancel the initial screen to select a database, then click File - NEW, click the Database Tab, select Inventory Control, you could probably use that built-in database. Just follow the wizard to create your tables and fieldnames.

 

[fneily]

About your structure:
I'd add a date field to the Inventory table to track transaction date/time.
In the Parts table, I'd add a Initial_Quantity field that shows how many on hand. Qty in inventory, as you state, will be the change amount.
You have multi-field primary key in the Inventory. They are not foreign keys. Make a single primary key for Inventory, say InventID, and the rest common fields. They're only foreign keys when they're common fields.

 

[geolemon]

I actually have a pair of tables ("process" and "transaction log") that I'm using to record not only the last change date/time, but to allow me to record a history of changes that could track through a customer or ISO (or internal) audit.
I actually didn't want to have a timestamp in the inventory table, because that table needs to reflect the current status, not necessarily just the status at the point in time that the inventory was updated. A minor difference in state definition perhaps, but important in developing a system that is fully integrated with business, inventory, and manufacturing processes - believe it or not, due to our ISO9001 quality systems, and their concepts for documentation of tracking parts through processes, that is actually closer to a requirement than an idealistic concept!

That being said, this database at this point is much closer to a concept than it is a working application. There are a great many details (including a mechanism for even logging to those log tables) not here yet. This is, at this point, a development environment so that I can figure out how to implement Access.

I do appreciate the scrutiny and suggestions, but trust me - not yet. This is a very simplified view, with just enough structure to represent the future application. In that light, I'm rather flattered that those comments are all you found to criticize, lol. What I've shown you here, is just a subset, not a complete list of tables. It's just a skeleton to understand the forms and mechanisms, and round out after the fact.

Interesting comment on the sample database - I'll have to take a look at it, if nothing else, I'm sure there are ideas in there!

Interesting comment on adding a separate (autonumber?) PK on the Inventory table - does Access not support compound PK's well? Cannot use them as foreign keys? It seems wasteful to have a meaningless / artificial PK column, and then perhaps difficulty in keeping duplicates (based on my current key definition) out of the Inventory table...
Strange, very strange - how do you mitigate these resulting issues?

Thanks!

 

[geolemon]

Something I'm not understanding - it seems like the combobox isn't behaving like I expected it to:

(the form is currently built with reference to a stored query - a simple "select * from..." all the tables I listed above, joined by PKs to the Inventory table.)

If I open my new form, and start to add a new part into inventory (new part number, new inventory record), I expected I'd be able to select the drop-down for [for one example] the Inventory Location field "Location", and I'd be able to select from (I assumed) a discrete list of locations in the PartsLocation table - or, I could type in a new location.

When I click the drop-down on the combo-box for that field, I get nothing - an empty list.

Do I need to re-define the record source in that combo box? Isn't that predefined, since I used a query to build this form from [using a wizard]? Obviously though, something isn't correct.

Also, when I'm inputting a new record, I'm not able to input an initial Qty (stored in the Inventory table). However, once the record is there, I can navigate to it and update the Qty.
Can I do something more sophisticated to avoid this problem? Possibly force the order Access writes data such that it populates the dimension tables before the Inventory table?
In the form, I've arranged the columns in that logical order, and changed the "Tab Index" values accordingly, so that this column is last in my form. Is there another "index" value I'm overlooking - something that would force this order, so I could have single-form / single-input action for my end user? It seems unprofessional to tell him to input the record, ignore quantity... save the record, pull it back up, and THEN he can input the qty.

Thanks in advance!

 

[fneily]

You might want to read:
Using Updatable Queries White Paper Available in Download Center

http://support.microsoft.com/kb/148581

As far as the Inventory Pk, I guess you don't need any duplicate data so you can use your multi-field PK.

For your combobox, always post your rowsource code. Also post what's in Column Count, Column Width(any 0's?) and Bound Column.

Inventory is be the Last table to be entered. All the other tables seem they can be pre-filled. After all, Inventory's fields are based on the other tables. You could have a tab control, have the first tables on one tab then another to do Inventory.

Also, you can search the Access forums on how to create a log(history) system.

 

[danvlas]

PHV's answer is the key:
"Look at the NotInList event of the ComboBox object."

My assumption:
Every combo box is connected to a foreign key irrelevant to the user but displays the most significant information about that key.
Therefore, each combo box would have a source like "Select ID, IDName From PrimaryTable"

ColumnCount: 2
Bound Column: 1 (that links the combo to the key)
ColumnWidths: 0 cm; 2 cm (This hides the key and displays the second column)

Now...there is a property of a combo called 'LimitToList', which is now automatically turned on, meaning your combo will only accept the valuse it 'knows' from the query.
Unless...you use the 'NotInList' VBA event to manage it:

Private Sub YourCombo_NotInList(NewData As String, Response As Integer)

'NewData is whatever you have typed, Response is the behaviour of the combo

If MsgBox("The information '" & NewData & "' has not been recorded in the db. Add now?", vbYesNo) = vbYes Then
Response=acDataErrAdded 'combo accepts new entries
'suppose there are more columns you want to fill in
'You need to open an entry form for that table, so:
DoCmd.OpenForm "SomeForm", , , , , acDialog, NewData
'the form will be a dialog, so your combo will wait till it closes down
' your form receives the 'NewData' in its OpenArgs property and you can use it there to populate the corresponding field. You let the user fill in all other required fields, then save the record and close the dialog
Else
Response=acDataErrContinue 'removes Access's default message
MsgBox "Please select something from the darn list"
End If

Basically that applies to all comboboxes - foreign keys.

To avoid the impossibility to add the qty when the record does not exist, I always use unbound forms. That way, you can type whatever you like whenever you like it.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html