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]

Hey Jackie!

Thanksgiving was quiet. We just puttered around, played on the computers, worked on a friend's very OLD computer so he can use it to surf the INternet (that was fun, no CD ROM drive and I needed to install Internet Explorer to it; so we hooked it up to our little network and got it working!)

**************************
Okay, first off... you can have just the CharterNo in the Transaction table and "show" the description without adding it to the table. So that way, the Transaction table is the only thing getting updated. I suspect the error you get is because the source tables have not been set right in the form's properties.

In the properties of the field is where you can say lookup this info but place it in this table... and place just the one code. The description field could "just show up" without being added to the table.

That being said, for using your second form, you need to copy the data in the CharterNo field to carry over to next form. The variable holding the data must be in a general module, not the form's module, so that you can share it between two forms.

Then you capture the field data once the user presses the button to open the next form.

And when the second form opens, you have the CharterNo field automatically fill-in based on the data in the variable.

Make sure on the second table that the table it points to is the Transaction table, not the Description table.

Does that make sense???

Mary

 

[jksb]

s-) (Just kidding - a bit!).

Okay, so I do have form one with these fields (which match the table, btw):

Inventory Description (Form Name)
CharterNo
OldCharterNo
VendorNo
Description
Vendor
Manufacturer
Cost
ReorderLevel

When I enter CharterNo, all the other info shows (thank you a thousand times for that!)

Second Form (Click on box)InventoryTransactions

TransactionNo (AutoNo)
Date (Auto)
CharterNo (ControlSource CharterNoIventoryDescriptionForm)
StartInventory
NoRecd
NoIssued
EndInventory (ControlSource SI+NR-NI)

I set these up using FormWizard, so it automatically linked for me. I'm assuming they are pointing to the right tables; how can I double check?

How do I get it to copy the CharterNo to the InventoryTransaction table instead of just showing it on the InventoryTransaction form? Do I do an AfterUpdate?

Sorry for the questions, but I'm in that brainddead space again..I keep looking at my forms/tables, and they look right, but just won't save right!

Thank you!!!!

Jackie

 

[jksb]

Okay, now I've got it accepting everything on my InventoryTransaction table except for CharterNo and EndInventory...both which get their data from ControlSource.
Is ControlSource not a good way to go with this, then?

Thanks!

Jackie, so close yet so far...

 

[Telsa]

LOL!!! Hey!! I KNOW how it feels! Been there way too many times.

Here goes,

In the main module, set up a variable for your CharterNo..

Key it like this...

Public strCharterNo As String

Do you know how to create a module? You go to the menu bar and click the Insert for a drop down list.. there you will see Module (don't select Class Module though).

Then you go to your button on the first form and add a line to capture the data in the charterno field and place it in the strCharterNo variable.

Then in the module of the second form, where the General drop down tab is, click and select Form. I think it defaults to creating a Form_Load procedure. There you copy the data from the variable into that form's CharterNo field.

Does that make sense?


Mary

 

[jksb]

X-)

Thanks! I'll give it a try...and will get back to you tomorrow on how it works! Have a good evening!

Jackie

 

[jksb]

Good Morning!

I'm attempting to do as you say, but am a bit confused (par for the course . I'm sure I'm not understanding your directions

I went to Insert and the only place I can find to create a Module is when I first open the db. I typed in

Public strCharterNo As String

and saved it. Is that all I need? When I open the Northwind modules, they have a lot of if/then statements.

Now, I'm not sure what you mean by going to the button on the first form and adding a line to capture data...when I open the form, InventoryDescription automatically opens up; there are no buttons. Also, I cannot insert a module when the form is open (it doesn't drop down under insert). Same for the second form (which opens up with a button on the first form) - it won't let me create modules from there.

And this was going to be a simple project! I am learning a lot!

Thanks!

Jackie

 

[Telsa]

Hi Jackie!

Okay, I see the confusion... what you need to do is open the Visual Basic IDE.

Try this: Select Module from your database collection (where you have Form, Tables, Queries, Macros, Module to select from). Then select New, that will open the Visual Basic IDE and a new module to program in (there are a couple of ways to create a new general module... that was another ). That is where you would have the code for Public variable. This module will load when the database is opened since it is not associated with any form.

Also, it is a good idea to have Option Explicit in there as well. Don't know if your program was set to default to have it in. If it isn't, then click on Tools, Options, and check the box with Require Variable Declaration. This will help you find typos in variables in a way. Because if you use a variable you haven't "Dim" yet, it will alert you to it. Neat little tool.

On the left of your IDE, you should have the Project Explorer. There you should see all the forms and modules you have created. You should see the your lookup form for the description as well as the form where you input transactions.

By button, I mean the button that is clicked on your first form that you have the lookup for the description on. In the Visual Basic IDE, find that form and double click on it. It should bring up all the procedures for the form's module (containing your Dlookup functions and such).

You need to click the down-arrow button on the (General) listbox to find the name of the button you made in that form.

That button should reference opening up your second form. Right before the code that does that.

Here you would code

strCharterNo = Me.CharterNo

then you would have your line to open the transaction form.

Me is referencing the focused form (didn't know if you knew that)

Now... you would next go to your second form's module. Make sure to change the first drop down to Form. The procedure it defaults to I think is Load. So it would like this

Private Sub Form_Load()

there you would transfer the data in your variable to the new field.

Me.CharterNo = strCharterNo

remove the controlsource name for your CharterNo field in your second form.

Does this make sense????

Mary

 

[jksb]

Whew! You are out there..I was afraid you took the day off or something

 

[jksb]

Whew! You are out there..I was afraid you took the day off or something . Well, now to finish my sentence...when I click on Module, the IDE does not show..it takes me directly into the Module. I will see if I can get the code to run (hopefully!) and let you know. Also, can I do the calculation from here, too? (EndInventory=StartInventory+AmtRecd-EndInventory) and have it save this in my InventoryTransactions table?

Thanks!
Jackie

 

[Telsa]

Naw... had to get my car into the shop

Hey, do you think there is a prize for the most responses in a thread????

I'm working a db right now involving statistics, so my PC can get bogged down sometimes.

Mary ;-)

 

[jksb]

Oh, and there is no button for the description to come up...I just enter CharterNo and it automatically brings up the correct description.

Jackie

 

[Telsa]

Actually, you are getting into the IDE... the modules are in the IDE.

BTW: What version of Access are you running? I forgot to ask before.

Mary ;-)

 

[jksb]

Well, I figured there are probably a lot of people out there in my position...just learning, and this is really great basic stuff..and it will help them. I know that when I read TekTips I was afraid to ask these more basic questions...everyone out there seems so knowlegdable, and I really feel like I don't want to waste people's time, but I know when I do keyword searches for some of this, most of it doesn't apply to these more basic questions. And everyone needs to start at the beginning sometime! You help is really greatly appreciated!!

Jackie

 

[Telsa]

Hey, I love to help! I know what it is like.

I bounce between several forums... this one, a-three, ZDNET, Access World Forums, for help. But seems I've always found most of my help here.

I've been in a learning mode for about a year now. I like to do complicated stuff, so I bought lots of books and ask lots of questions. Many times I figure it out before I get an answer, but it is nice to see what other peoples' ideas are.

I've taken a few Visual Basic classes at SMartPlanet and that helped me a lot. So when I'm doing VBA, it comes together a little quicker.

I also study other people's code to try and see what they did and why they did it that way.

If I could make money showing people what I have learned, I probably would quit my job! But then again, the job is what gives me the opportunity to explore this arena! LOL

Just keep on asking, many people like to help out; you can tell by all the tip sites out there!

Mary ;-)

 

[jksb]

Back again...Access 97!

Jackie

 

[jksb]

Okay..first problem..no Project Explorer (and I can't get it to show in Help). The right list box has "General" in it and the left has "Declarations". Those are my only options. Nothing else shows. Argh!:-o

Jackie

 

[jksb]

OKay, if I go into each form under design, and click on properties of that control, then I can find all the details you are talking about, but the Module is a Class Module, and I'm not sure that's what you want me to use. I did put in the anyway (to see) and it doesn't recognise the strCharterNo in the second form. This is what I have (I dim/stringed it).


Sub ToggleLink_Click()

Dim CharterNo As String

strCharterNo = Me.CharterNo

On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

ToggleLink_Click_Exit:
Exit Sub

ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit

End Sub

Also in the first form:


Sub Form_Load()

Me.CharterNo = strCharterNo

On Error GoTo Form_Load_Err

If ParentFormIsOpen() Then Forms![Inventory Description2]!ToggleLink = True

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit

End Sub

Actually, I just had to remove them because I couldn't open the forms with them And no, I didn't write these; they were done already by Access LOL!

Jackie

 

[Telsa]

Okay, Access 2000 has it... 97 doesn't. Fortunately, I have two computers and my other one has 97 on it. So it doesn't look like you have that option... that's okay.. it's just convenient.

Since you did create a new module, go ahead and put that Public variable line in.

Then we need to go to your First form where you search for the right CharterNo.

* Open the form in design mode and click on the button.
* Then open its properties.
* Then scroll down to where the event procedure is and click on the ellipses (the three dots) and this will open the button in the Form's module.
* Now you are in the button's procedure and just need to add the line to capture the CharterNo field's value and place it into the strCharterNo variable.

NEXT

You need to go to your second form in Design Mode.

* CLick the little square in its upper left corner to select the form.
* Then if the properties is not open yet, open the properties of the form.
* Scroll down to On Load event and click the ellipses. Here is where you place the code that copies data from strCharterNo to the form's CharterNo field.

How's that????

Mary ;-)

 

[jksb]

OKay, if I go into each form under design, and click on properties of that control, then I can find all the details you are talking about, but the Module is a Class Module, and I'm not sure that's what you want me to use. I did put in the anyway (to see) and it doesn't recognise the strCharterNo in the second form. This is what I have (I dim/stringed it).


Sub ToggleLink_Click()

Dim CharterNo As String

strCharterNo = Me.CharterNo

On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

ToggleLink_Click_Exit:
Exit Sub

ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit

End Sub

Also in the first form:


Sub Form_Load()

Me.CharterNo = strCharterNo

On Error GoTo Form_Load_Err

If ParentFormIsOpen() Then Forms![Inventory Description2]!ToggleLink = True

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit

End Sub

Actually, I just had to remove them because I couldn't open the forms with them And no, I didn't write these; they were done already by Access LOL!

Jackie

 

[Telsa]

Class module is okay for the Forms (that is what they are) ... but you need a main module created from Module. This way it will available for all procedures.

Looks like you selected a Toggle button instead of a command button. Command button would be better.

You want the reference to capturing data in the button procedure, not in the Form Load procedure, of your first Form. The second FOrm is the one where you place the code to copy the data to the field on Form Load.

Does that make sense??

Mary ;-)