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]

Okay..if I tell the second form to get CharterNo from form one as the control source, it does what I want *except* save the CharterNo in the InventoryTransactions field for it. Is there a code to tell it to do this? I know I went around this before, but it just seems like it *should* work.
Or do I need to do a DLookup in the InventoryDescription form? Is that possible? I tried it but it's not quite right.

Thank you again big time for all your help!

Jackie

 

[Telsa]

Jackie,

Is the Public code in a Module? I mean did you create a new module and type in the code


Public strCharterNo As String

Because that is the variable that can be shared between the two forms.

Mary

 

[jksb]

Good Morning ??(I'm having one of "those" mornings!)

Yes, it's in the Module.

Okay, what form 2 does is search the Inventory Transaction table for the CharterNo, and if it finds it, populates the form with that record, but if there is no match, gives me an empty record (new transaction no)- with an empty CharterNo field. If I type in the CharterNo, then it will save the CharterNo.

Help! Had to have someone come last night to fix my computer...loaded an old Dos program for my son to play, and it crashed my hard drive. What a night!

Hope you had a better evening!

Jackie

 

[Telsa]

Hey Jackie!

But if you use the lookup form to look up a CharterNo and then once found, press a button to launch the second form to create a transaction, does the CharterNo in the Transaction Form get populated?

(Know what you mean about crashes... I finally decided to do a total reload on my PC last night... reformatted my drive and am reinstalling my operating system and programs... Was there a full moon last night????? *G*)

Mary

 

[jksb]

Not if it's a new record. If it's an old record, it brings up that old record. Does not automatically enter a number. But I want a new record for each transaction.

I was looking around and found that the record source is the InventoryTransactions table. Would writing a query to get the data from the InventoryDescription Form instead work?

As for the full moon..I think it's out those few weeks before holidays so you *have* to go over budget and use your credit cards for stuff. Let's see..maybe it's a holiday virus that infects all mechanical things...released by these megaconglomerates retail money mogels ...triggered off by shorter hours in the day...maybe the megaconglomerates have arranged for the space station to release it...Ha! Maybe I should write for Chris Carter and the X-files LOL!

Jackie

 

[jksb]

Well, I can get it to do the CharterNo match if I use Control Source, but it won't save the number in the Transactions Table, and it still pulls up old records if I enter a number that is already in that field. Argh! And this is a simple code; I can't imagine trying to write the difficult ones.

Thanks again for all your help!

Jackie

 

[Telsa]

Maybe make a form that uses the query to lookup transactions maybe. I've never tried editing data in the query before.

If you do make a new form for looking up existing data in the Transaction table, you can then put in the

Me.AllowEdits = false
Me.Allowadditions = false

in the form's load event to prevent accidental editing. You'll have to create an Edit button that resets the Me.allows to true.

It's a thought...

I like your theory! You DO sound like Fox Mulder! Shame he is staying in the background these days in the episodes. Not sure I like Doggett... no sense of humor.

Mary

 

[jksb]

I don't know. It seems to me that there is an obvious easy way to do this. Can I do a "D-Lookup" on a form (form 1) from another form (form 2)like I can a table? Is there a way to write code that says if the CharterNo field is full on the Transaction table (and in theory, once we get it to populate the table, it will be), then start a new record?

Thank you big time! I'm going through the Northwind DB to get some more ideas...

Jackie

 

[Telsa]

Well, you can say...

Dim stDocName As String
Dim stLinkCriteria As String


If not isnull(strcharterno) then
stDocName = "TransactionForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria


and in the Transaction form on the form_load event, say

If not isnull(strcharterno) then
me.allowedits= true
me.allowadditions = true

else

me.allowedits = false
me.allowadditions = false

See what I'm doing??

Dlookup is for tables/queries only. Wouldn't useful here. That public variable is what you need to use.

Just an idea...

Mary

 

[jksb]

I am getting an "Method 'Item' of Object 'Forms' Failed" error, and when I close the form I get "The expression "On UnLoad"as the event property setting produced this error: "Invalid Outside Proceedure". Okay, this is what is in my second form (I figured it would be easier for you to just see it):


Sub Form_Load()
On Error GoTo Form_Load_Err

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

If Not IsNull(strCharterNo) Then
Me.AllowEdits = True
Me.AllowAdditions = True

Else

Me.AllowEdits = False
Me.AllowAdditions = False



Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit


End Sub
Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_Unload_Err

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

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Error$
Resume Form_Unload_Exit

End Sub
Private Function ParentFormIsOpen()
DoCmd.GoToRecord , , acNewRec
ParentFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, &quot;Inventory Description4&quot And acObjStateOpen) <> False

End Function


Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

strCharterNo = &quot;&quot;
End Sub



Most of this was automatically put in by Access. Thanks!

Jackie

 

[jksb]

Okay, if I set this up as a form/subform, it works. I don't know why with linked forms it doesn't. I'm not crazy about having to click &quot;subform datasheet&quot; to get the form view up for the subform. Do you know a way to get it to stay when I close the db? Also will need to get it to calculate. One more thing..how do I get it to automatically give me a blank form (right now it's populated with the first record from the db).

Hey, looks like I(we) are close! Thank you!

Jackie

 

[Telsa]

Jackie,

The code below will open a new record... place it in the form module

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub

Also, I think the function

Private Function ParentFormIsOpen()
DoCmd.GoToRecord , , acNewRec
ParentFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, &quot;Inventory Description4&quot And acObjStateOpen) <> False

End Function


you have for in the form module should be in a main module, like where the Public variable is located.

Don't forget to put in End IF after the IF statement! It should be


If Not IsNull(strCharterNo) Then
Me.AllowEdits = True
Me.AllowAdditions = True

Else

Me.AllowEdits = False
Me.AllowAdditions = False

End If

Mary

 

[jksb]

Good Morning! I'm Baaack! I am in the middle of a project, but did take a minute to see if this worked, and it does! Thanks!

I am seriously thinking about instead of have Acess do my calculations, of using Excel and importing the data. It seems like an easy procedure. My only problem with that is there are no nifty forms to work with. Can I set up the forms in Acess so it delivers the data to Excel, does the calculations, then delivers it back to Access? I havn't had a chance to get the formulas you gave me to work yet; I'll tackle that this afternoon (hopefully).

Are you getting the snow we are? Looks like a white winter (finally!) here this year!

Jackie

 

[Telsa]

Actually, you can do calculations easily enough in Access reports or forms. Just create an unbound textbox and key in your calculations there. You may want to practice in Excel to get the idea how it should flow in Access.

We got snow before Thanksgiving but nothing but fog and smog since. They say we'll have snow this weekend.. hope so... be good for ski resorts and water supply!

Mary

 

[jksb]

Hi Mary!

I haven't forgotten &quot;the world's longest thread &quot; but have been busy with another project at work. I plan on tackling and finshing this db for the New Year. Hope you have a happy holiday!

Jackie, in Snowy Michigan!