Basic Database Help Needed! 1

[jksb]

I really appreciate any help; I hope these are not too basic questions, but I have read and reread the book for hours and they are just not makeing sense suddenly.

I've got 3 tables I am working with: Inventory
Description, Inventory Supply, Inventory Transactions.

I am attempting to make form like the Northwind Orderform, where when the ID# is typed in, the description, vendor, etc, appear. These are the fields I want to show on my form:

Date/Transaction#/ID# /OldID#/Vendor#/Description/Vendor/Manfacturer/#Recd/#Issued/
BalanceInStock/ItemCost/ReorderAmt/TechID/ContractorID/Approval#/Node/TruckStock.

My tables include:

Inventory Description:
ID#/OldID#/Vendor#/Descp/Vendor/Manuf

InventorySupply:
ID#/#Recd/#Issued/Bal/Cost/ReorderAmt

Transactions:
TransID/Tech#/Cont#/Appr#/Node/TruckStock

I want all transactions stored so I can bring them up in a report if necessary. I know about BeforeUpdate/AfterUpdate, but do I need to make
another table for this to work, or does it do so automatically? (I was thinking of storing the data on the Transactions table). Also, after looking at the above I think I need InventorySupply to have TransID as a foreign key.

Also, am working on the form...I am having a problem getting the fields to link so that when I type in ID# all the rest of the info appears. At this point I am totally confused...do I need to make a query? I can't find one
in the Northwinds DB to refer to. I though I would use subforms, but how do I get them to link? I went into Northwinds and checked out the properties; but that even confused me more. I also thought of subforms but don't know if that's what I need or just one form that will bring up all the data. I also want a combo box for the ID#; but I don't want the ID#s to change when typed in, just used as a resource to find the rest of the data. The only thing that will be variable is the #Rec'd, #Issued, Tech#,Cont#,Approval#, Transaction#, Node#, Truckstock.

I *really* appreciate any help you can give me. I know this is very easy for you, but for some reason I've hit a mental block with it.

Thanks!

Jackie

 

[Telsa]

Yup!

You need to create variables and use them, then at the last part of code copy data from variables into the form's field.

That is why we created those two variables... strCharterNo and txtCharterNo... although might be a good idea to rename them to reflect what the data actually is... like

strCharterNo is your data captured from your CharterNo form field and maybe create variable called strOldCharterNo for use in capturing the data from the table.

The more intuitive you make the variable names, the easier it is to remember what they are for.

One thing I was thinking... change the type of data populating the variables from String to Variant. Variant is a kinda catchall... it handles all kinds of data. That might help resolve your NULL problem.

Mary )

 

[jksb]

Yeah!! It's working! Only problem...if I go back to the CharterNo that is a blank field, it will leave the OldCharterNo instead of giving me a matching blank field. How do I get it to clear the field if it's blank?

This is what I got to work:

Private Sub CharterNo_AfterUpdate()

Dim txtCharterNo As String ' This is to capture data from the table
Dim strCharterNo As String ' This is to capture data from field on form
Dim varoldCharterNo As Variant

strCharterNo = Me.CharterNo ' Copy data in this field to variable strCharterNo

varoldCharterNo = DLookup("[OldCharterNo]", "Inventory Description", "[CharterNo] = '" & strCharterNo & "'&quot

If (Not IsNull(varoldCharterNo)) Then
Me.OldCharterNo = varoldCharterNo
End If

End Sub


And now that I am ready to move onto the next field...Description...will I

DIM varDescription as Variant

then put

varDescription = DLookup("[Description]", "Inventory Description", "[CharterNo] = '" & strCharterNo & "'&quot

right after the DLookup for OldCharterNo, and so on and so forth?

Thanks!

Jackie

 

[Telsa]

As far as clearing the fields, my way around that was to create buttons for adding, saving, editing, and deleting records. That way I could put code to have the fields = "" or nothing when I added a new record. You can use the Wizard to create these buttons, then just edit the button code for Saving to empty the fields.

You are right on with the coding for your next field! Very Good!

Hey, we'll make a programmer of you yet!!

Mary )

 

[jksb]

Thanks I would like to be a programmer...I've always enjoyed this; am only working as a temp here, and took project on not only because I felt I could do it, but also because I think the best way to learn this stuff is by doing it. I'm assuming when I feel comfortable enough with Access, I won't have much trouble finding jobs ...here in Michgan (especially on the western lakeshore) there seems to be a high demand for people comfortable with Excel and Access.

I will need to add a save button - when my warehouse mgr adds data (I have two other subforms for inventory amts) he'll need to save it. I'll figure that out next . I'm just concerned it's not giving me an empty field for OldCharterNo and I'm afraid someone will accidentally save it with the wrong number there. I would like that field to be blank if it's Null.

Also, do I need to add any more
varChartNo=me.CharterNo
type codes, or is this okay as it is looking up all the other data based on the CharterNo?

Thanks again!

Jackie

 

[jksb]

Oh, one more thing...I need records of each transaction stored in a table so I can access daily inventory. Which would be easier...BeforeUpdate or AfterUpdate? I'm thinking AfterUpdate, so it has a TransactionNo. Also, do I have to tell it to save it in the Tranaction Table, and how do I do that?

Thanks you again!!!!!

Jackie

 

[Telsa]

You will need to create a "lock" on the record. When you have the form load, go to the Visual Basic module for the form and add

me.Allowedits = false
me.allowadditions = false
me.allowdeletions = false


Then when you create the buttons for adding records or editing a record, add the above lines but have it equal true. That way your form becomes "idiot" proof!

You are right, you can use the same variable over and over for CharterNo as it resides in the same procedure. That helps to reduce the volume of variables you create.

I found the a great learning tool was John Smiley's book Learn to Program Visual Basic. He does it in a story book format and you actually create a working program from start to finish. Most of the stuff you learn there is usable in VBA for Access and Excel. It comes with a Working copy of Visual Basic 6.

He also has a site he keeps updated with news and stuff called

http://www.johnsmiley.com

I found him EXTREMELY helpful when I got stuck on stuff.

Another book I found helpful after I finished his books was Beginning Access 2000 VBA (they have a 97 version out too).

If you can, you might want to think about taking some online VB courses at SmartPlanet.com they are cheap and very effective. And John teaches there as well!

Mary

 

[jksb]

OKay..I've got the data entering fine; however when I want to go to design view, it tells me it can't perform cascading operation because it will create a dup key (which prevented on the table). I can't get into design view unless
I go to the first number.

Also, where do I put the locks? I tried it in where I am writing the codes, and it ignored it.

Thanks! Can you believe only one more day to the long weekend...maybe I'll actually have this working by then, with your help!

Jackie

 

[Telsa]

Hey Jackie!

What table is this form populating with data?

The locks will go into the procedure for Form_Load called Private Sub Form_Load(). On the upper part of the Visual Basic module, there are two drop downs. The first is for the controls and objects and the second is for the events the controls and objects can do.

If you click on the first one, find Form, it will take you to the Form_Load procedure (which is the default procedure for Form). There is where you can lock the editing and adding.

Your next step is to create buttons on the form. Then on the "click" event, you would "unlock" the record so it can be edited. The Wizard is very helpful in creating Add and Delete buttons but not so with Edit button. But you can create that manually.

I can hardly wait for the 4-day! Gonna get some stuff caught up. My daughter and her family are visiting her husband's family for the holiday (about a two-hour drive from here). So it will be just my husband and me.... and our two dogs.

Mary )

 

[jksb]

There are two tables...one is InventorySupply (which is the total in stock inventory) and the other InventoryTransactions (keeps track of who gets what). Should I combine the two tables? The Unleashed book said to break the down as small as possible, but I can easily merge these two. It would also only give me two subforms to link as opposed to three.

Also, I am trying to link them but it won't because they are unbound. I am trying to bind them now by following Help, I do what it says (go to Record Source, click on Source) but when I go back to link, it tells me they are still unbound. I'm still "researching" this but didn't know if you had a shortcut for it

Thanks! Tgving will be small...my two kids (7 & 11) & me.
And all my beasts...2 dogs, 3 cats, etc. (I do rabbit and guinea pig rescue to help my shelters out).


Jackie

 

[jksb]

That was silly...I was looking under "bound controls"! Been staring at the help man too long!

Jackie

 

[Telsa]

Jackie, do you have a "key" setup for each table? I would combine into one table because it makes for easier reporting. But use the "key" of the related tables to link it all together.

In the database I created, in my form I have several tables I pull data from but it all populates one final table based on the key. This way when I create a query to be the backend to my report, then I pull those tables in to get the full information I need for my report.

Does that make sense to you?

Mary )

Great you're an animal lover! We are too... that's why we became vegetarians. I had guinea pigs for pets when I was growing up. Cutest, cuddliest things you ever saw! And VERY smart!

 

[jksb]

Okay, I have *no* idea how I did got them bound, but I did.
Glad I'm going home early! Have a nice evening!

Jackie

 

[Telsa]

Hey you too! Have a great time off!

Mary

 

[jksb]

. So the "key" table would store all the data in it...the changes, etc? Or do you mean Primary Key Field...that's CharterNo. All three tables have that in common.

I'll get back to this tomorrow!

BTW, We're vegetarian, too...Tofurky for T'giving for us!
Jackie

 

[Telsa]

Tofurky! Us too!!!

Each table has a "Key" where it makes the record unique from every other one. The data in the key is Indexed (sorted) and not duplicable. The best key to have is just an autonumber field. That way you can change the data in all the other fields.

Your CharterNo key is a good Primary Key. And it is useful to gather related data.. as you saw in the Dlookup function.

SO when you have a form gathering data... you can use the Key to be the only field from that table to populate the underlying table of the form. Then you have other fields that you would use to add additional data that would not be of the Inventory Description table.

Then when you create a query for a report, you can pull in the Inventory Description table and the table populated from the form. There you can add all the fields from the Inventory Description and those you need from the other that would be in your report. Just make sure you connect the two related fields.

Does that make sense???

Mary )

 

[jksb]

Good Morning!

I think so...one table contains all the "unchanging" data...Inventory Description. The second table, "Inventory Transactions" will contain all the changing, "new" data.
Primary key in Inventory Description is CharterNo, Primary key in Inventory Transactions is TransactionNo, Foreign key is CharterNo. Since each transaction will have it's own number, it will automatically store it, and I won't need to write a code for the transactions to be saved somewhere else.

Now I'm going to go play with it a bit!

Thanks!
Jackie

 

[jksb]

Okay..I will have three tables. Inventory Description, the "parent" table, Inventory Transactions, and Inventory Supply. I tried combining Inventory Transactions and Inventory Supply but it assigned Transaction numbers to each item, and that's not what I need. So to answer your question, I'll be populating both - one based on supply, and one based on actual transacton info..who took it, where it went.

Jackie

 

[jksb]

Hello again! I"m baaack... ;-)

I've set it up my db with two tables...one with Inventory Description, the "unchangeable" data, etc, and one with Inventory Transactions, which has all the "changeable" data. The common linking field is CharterNo, primary key in Inventory Description, foreign key in Inventory Transactions, whose primary key is TransNo(which is autonumber).

I have now two linked forms, main form InventoryDescription, which is the one when you type in CharterNo, the description appears. The second form (which shows when the button is clicked on) is the InventoryTransactions. This is where I would like the new data to be entered...NoIssued, NoRecd, StartInventory, EndInventory, etc. I first set it up without CharterNo (in second form) but when I tried to save it, I kept getting NULL value not accepted...it seemed like was trying to save the transaction in the right form, but because it was not copying the CharterNo over from the first form. So I then added CharterNo in second form (which is fine...a double check for the enterer), but no I need to get it to match/link the CharterNo on the first form, so the changes get credited to the proper item.

To try to be clearer...when the CharterNo is entered on the parent form, I want it to also show up in the CharterNo space on the child form. Of course, when I save it (I want to add a save button, too I want to tell the form to save it to the InventoryTransaction table.

I hope this makes sense! Thanks for the help!

Jackie

 

[Telsa]

HI Jackie!

I'm wondering why you are using two forms rather than one? Especially since you are populating only one table.

Mary

 

[jksb]

Hi! First, several reasons (at least what is happening with me

If I use one form, when I save it, it wants to overwrite all my "unchanging" data..CharterNo, Description, etc. I have those tables set at No Duplicates; so I then get it kicking back that I can't save it because of that.

I also don't want all the extra data (such as description, etc) saved for each transaction...so I thought the first form would open so the data person could be sure it's the right description, then the second (child) form for the data being entered. I'm sure I could write a code to make it work, but I thought this would be easier.

Third, my warehouse person doesn't want to be tabbing all over the form; this way he just needs to tab only the data entry part instead of tabbing across descriptions, etc.

Whew!

I figured out that telling the second form to get the info using Control source worked. . Now, when I go to save it, it tells me it cannot save at this time because "InventoryTransactions1.CharterNo can't contain a Null Value because the required property is set to True. Enter a value in this field"...but the CharterNo is there.
How do I get it to use the CharterNo to populate the field?
Is it recognizing it at all as populating the field?

It's also telling me it cannot save because No Duplicates (CharterNo) are allowed...but I set the InventoryTransaction to Duplicates Okay. Is it still trying to save it to my InventoryDescription table, too?

Sorry for so many questions again..I got a lot done, and can see the end in sight, but these are all those little details that are bogging me down I would love to get this done this week.

Hope you had a good Thanksgiving!

Jackie