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

 

[jksb]

Yeah! That part worked!! Except it still won't save it on my InventoryTransaction form...I think that the first form, which has the description, is trying to save to the InventoryDescription table (which I don't want it to do - I have it set to prevent duplicate numbers)and is kicking it all out. How do I tell it to only save to the InventoryTransactions table, and to ignore the rest?

Also, I want it to calculate..I tried to put the formula I want in OnLoad:

EndInventory=[StartInventory]+[AmtRecd]-[AmtIssued]

but I get "True" appearing in the control.

But we are getting there..only 4 more little things to go!


Thank you!!!!
Jackie

 

[jksb]

Re Buttons :I used Form Wizard and it didn't give a choice. It actually is want I want...they don't click on it until they get the right number; then they open up the second form and do the transaction.

Jackie

 

[Telsa]

In the field of your first form for CharterNo, does it have an event attached to it? If it does, you would need to remove it.

Using On Load tries to calculate before you have put data into the form. I'm thinking that the time you want to have the formula work is when all the fields have been filled in.

Maybe put the formula in the forms module for the last field to fill in (NoIssued?). But have it check all the fields needed for the formula to be sure they have data before calculating and placing the answer in the final field.

Something like

Private Sub NoIssued_LostFocus()

IF me.startinventory > 0 and Me.NoRecd > 0 and me.NoIssued > 0 then
Me.EndInventory=([StartInventory]+[NoRecd])-[NoIssued]
end if



Or

maybe the procedure could be after update (Sub NoRecd_AfterUpdate())... You could put the same formula for all three fields so that if one gets update, it will automatically recalculate

Might be a good idea to create variables to capture each field's data for this. Be sure to set as Integer.

Does this make sense????

Mary ;-)

 

[jksb]

Good Morning! Hope you had a nice evening!

First - yes, there is an event...the one that pulls up Description, VendorId, etc. (the one we started off working on . I really can't remove that.

I was wondering if we could tell it to save the data on form 2 in InventoryTransactions, only using the CharterNo in form one on the InventoryTransactions table? Would that bypass the problem?

Second...I put the code in the fields, but it keeps kicking out me.startinventory as a compile error; Method or Data Member not found. I Dimed all the fields as integers; it kicks it out even with:

intStartInventory=me.StartInventory

Is this because I did not put anything in the StartInventory field?

Thanks again!

Jackie

 

[Telsa]

Hey Jackie!

Haven't forgotten you... been one of those days.

Was wondering how are you looking up the CharterNo? Do you scroll through the records or do a find (using binoculars from toolbar)? Thing of it is, we can have a simple form (without the dlookup) that scrolls through the Description table then capture the CharterNo when you are ready to create a transaction.

Just thinking...

Mary )

 

[jksb]

I know...I've been busy too. I actually do have the inventory in Excel (which is what I am working on today), but I wanted it to do a look up and someone told me to tackle Access. So here I am.

Anyway, I'm assuming it's scrolling...is that what Dlookup does? No, I'm not using binoculars, tho after getting this database running I may need them to see LOL (sorry, couldn't resist that one).

End of day for me...I'll be baaaccckk tomorrow Have a good day, what's left of it.

Jackie

 

[Telsa]

Well, if you create a simple form that has all the fields from your table, you don't need dlookup. YOu can create a button to do a find on the CharterNo field. Then once the right record shows up, you can have a button that captures that CharterNo data and then open the next form to start the transaction input.

This way you won't get errors because you are adding a record to the Description table. You'll just be looking up existing data.

That sound better???? Let's simplify!!

Mary ;-)

 

[jksb]

Hi! So with the button I would do the Dlookup code we wrote before?

I'm doing inventory now, so I probably won't get to this until Monday..have a nice weekend and Big Thanks!

Jackie

 

[jksb]

Good Morning!

Okay, got the command button to work perfectly on the first record. Typed it 1001, it searched, came up with the data, I opened the second InventoryTransaction form, the number 1001 was there; it stored it on the Transactions Table fine. Told it to search the second record, 1002, it did the search fine, but when I clicked the second InventoryTransaction form, the CharterNo did not come up. I even tried changing ControlSource to the first form CharterNo and it didn't work. It's giving a transaction number, but not saving the CharterNo to the table. This is what the ControlWizard coded:


Private Sub Command17_Click()
On Error GoTo Err_Command17_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

It's almost as if I need to tell it that upon opening second form, get the CharterNo from the first form. I do want to put a save command button on the second form, so that if the data is wrong, it's not automatically assigned a TransactionNo. unless I save it. Hope this is clear

Thank you! Hope you had a good weekend! Mine flew too fast..I feel like I never left here

Jackie

 

[Telsa]

Hi Jackie!

Do you close the second form before you start the searching again???

Wizard is great about putting Save buttons on. But you will need to edit the Form_load event for the transaction form so it will say

Me.Allowadditions = False
Me.Allowedit = false

Then add an Add New Record button so that you can undo the above... and undoing the above is putting the same code in but changing it to True.

Our weekend went way too fast too. My husband had to work on Sunday for a few hours. At least we got to see a movie on Saturday.

Mary

 

[jksb]

Yes, it's closed. It's not capturing the CharterNo from the first form.

Thanks!

Jackie

 

[jksb]

Oh, and do you mean "Undo Record" as opposed to "Add New Record"?
Thanks!

Jackie

 

[Telsa]

Jackie,

Try clearing out the variable after the form is loaded...

strCharterNo = ""

That may reset it.

Also, that code you have for the button, which form is it on??

Mary

 

[jksb]

Okay, I'm getting Compile Error: Method data or member not found. This is how I set it up, maybe I have the Me. codes in the wrong spot? I did try it before the On Error and got the same thing:

Sub Form_Load()
On Error GoTo Form_Load_Err

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

Me.AllowAdditions = False
Me.AllowEdit = False

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit

End Sub

Also, the Search button is on the first form; the second has the save button.

Also, being totally dense about what you mean by "clear variable after form is loaded"...do you mean to dim and then add strvariable=variable (i.e., strCharterNo=CharterNo).

Thanks! This is really coming together..however, next is to get it to do simple math so be prepared LOL!

Jackie

 

[Telsa]

Jackie, remove the allowadditions and allowedits (it was a typo, by the way, me.allowedits (plural))

I think it would cause problems if you want to populate the form with the field.

On the Save button code, let the last line after saving the record say...

strCharterNo = ""

We want to empty it so that it will be ready for the next record.

Mary

 

[jksb]

I'm baaaccckkkk...

It's still not finding the CharterNo in the second form.
And it doesn't like the

strCharterNo = ""

I get...

"Compile error..variable not defined"

Do I need to dim CharterNo?

Thanks!!!! (-:

Jackie

 

[Telsa]

Okay,

Take a look at your module (not the form's coding) and be sure the strCharter has been set there. It's behaving as though you hadn't created a module and placed the following code in it...

Public strCharterNo As String

Mary )

 

[jksb]

Okay..I see what it's doing. When I open the second form (InventoryTransactions), it is actually pulling up the old transaction instead of making a new one. When I save, it saves over the old data, not making a new record.

Which just made me realize another thing that might make it a bit more complicated. What I want to do is a current inventory that is ongoing...thus, I will also need to make another field in the InventoryDescriptions table to save the current Amt in stock, so that when I go to enter the new data, that is the figure I will be adding/subtracting from.

Agh!!

Jackie

 

[Telsa]

Okay,

Right before the command to copy the charterNo into the field... add this line...
DoCmd.GoToRecord , , acNewRec

That should create a new record and populate the field.

Sounds you are right about adding the quantity of inventory field to your description table.

Mary

 

[jksb]

Almost..

What happens is that it is clearing out the first form when I click to open the second form.

I tried putting that command under OnLoad, but nada. It is still matching up the number on the Transaction Table, instead of giving me a blank record and automatically adding CharterNo to it.

Ack! It's only 4:38 LOL!

Jackie