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]

Oh! I mean that you just replace the one line with the other. Add the THEN statement

If Nz(txtCharterNo,&quot;&quot <> &quot;&quot; Then
Me.OldCharterNo = txtCharterNo

There is nothing inside the quotes.

Mary

 

[Telsa]

and add

END IF

It is Monday isn't it???

Mary :-}

 

[jksb]

Okay, being totally brain dead right now...replace the dlookup line with what you suggested? Or the If(Not IsNull) line?

Jackie

 

[jksb]

Okay, don't answer that last question Jackie

 

[jksb]

Back to &quot;Invalid Use of Null&quot; Do I have something wrong
with what I'm doing?

Jackie


Private Sub CharterNo_AfterUpdate()

Dim txtCharterNo As String

txtCharterNo = DLookup(&quot;[OldCharterNo]&quot;, &quot;Inventory Description&quot;, &quot;[CharterNo]='&quot; & CharterNo & &quot;'&quot

If Nz(txtCharterNo, &quot;&quot <> &quot;&quot; Then
Me.OldCharterNo = txtCharterNo
End If

End Sub

 

[Telsa]

LOL!

Just the Not IsNull line... not dlookup.

Here is what it is doing...

Dlookup looks up the data and adds the data to the variable by matching the data you have entered (in Me.CharterNo) against the tables (Inventory Description) field (CharterNo). If it finds a match, it places the data from the field you want to get (OldCharterNo) into the variable (txtCharterNo).

Then the IF statement checks to make sure there is data in the variable and if there is, it will populate the destination field (Me.OldCharterNo) with the data copied from the field (OldCharterNo) from your table (Inventory Description).



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

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

txtCharterNo = DLookup(&quot;[OldCharterNo]&quot;, &quot;Inventory Description&quot;, &quot;[CharterNo]='&quot; & strCharterNo & &quot;'&quot

If Nz(txtCharterNo,&quot;&quot <> &quot;&quot; Then Me.OldCharterNo = txtCharterNo

End Sub

I added a variable to capture your field data. Otherwise, you'd get an error on the criteria of Dlookup.

Mary

 

[jksb]

OKay, I stopped getting error messages (I changed the first line of Private Sub CharterNo to txtCharterNo (it kept kicking back as an ambigious statement? and this stopped it) BUT it's now not populating Old Charter No. I even changed it and told it to populate Inventory Description, and that didn't work either. Just stays blank.

Sigh. Guess it's really Monday! And I really appreciate your assistance!

Jackie

 

[Telsa]

Okay, let's clear some things up....

1. What is name of field (from Properties dialog box) that your CharterNo data goes into?

2. What is name of field that you want to populate the Old Charter No. in?

3. What is name of field in the Inventory Description table that contains old Charter No?

4. What is name of field in the Inventory Description table that contains the matching number from your form's field (from question No.1)?

I suspect we have some naming problems here.

Mary )

 

[Telsa]

Hey Jackie:

I went and looked at our previous messages and see that your Inventory Description is not matching the names in the DLookup function. So I suspect that could be a problem (unless you have renamed them.)

I rewrote the code, just in case



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

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

txtCharterNo = DLookup(&quot;[OldID#]&quot;, &quot;Inventory Description&quot;, &quot;[ID#] = '&quot; & strCharterNo & &quot;'&quot

If Nz(txtCharterNo,&quot;&quot <> &quot;&quot; Then Me.OldCharterNo = txtCharterNo

End Sub

Can you see what I have done and why?

Mary )

 

[jksb]

Hiagain!

I'll answer 1st questions 1st:
. What is name of field (from Properties dialog box) that your CharterNo data goes into? CharterNo (I type in the No and the rest of the info is to come up)

2. What is name of field that you want to populate the Old Charter No. in? OldCharterNo

3. What is name of field in the Inventory Description table that contains old Charter No? Inventory Description

4. What is name of field in the Inventory Description table that contains the matching number from your form's field
CharterNo



I am going to go and try your new code only changing the OldID# to OldCharterNo to match.

Thanks!!!!!

Jackie

 

[Telsa]

Jackie,

On question 3, is the name of the field for the old Charter No. called Inventory Description??? Or is also called OldCharterNo?

Mary )

 

[jksb]

Yes! It worked!!!!! Thank you! Etherflowers to you!!!

Now I'll go play with it...I do have one more question for you .

I have three subforms; when I imput the CharterNo I'd like them all to bring up related info (the one we were working on is Inventory Description; the next one is Inventory supply, which has CharterNo, then supply info (in/out/reorder amt, transaction No. as a foreign key, etc.) The last one is Transaction Info...Trans No., Who issued to, that type of thing. How do I link them all together so all the related data will show when I enter the Charter No.? Trans Info Table does not have CharterNo in it, but I can add it.

Agh! Just went and typed the original CharterNo. in and gave me invalid use of Null again! Is that because the OldCharterNo for the first CharterNo is blank? I think that's the problem, as I just went and tested it with ones I know have blank fields.

There's always something, isn't there

You've been great so far, thank you! It's the end of the day; have a nice evening!

Jackie

 

[jksb]

Oh, sorry...the Table is Inventory Description (I thought that's what's needed..it to be told what table to go to.) The fields all match...OldCharterNo is the same everywhere.

Thanks!
Jackie

 

[Telsa]

Hey Jackie!

Glad you getting some success.

I have always used the wizard to join my subform to my parent form. IT has to have at least one related field.

Yeah, Nulls are fun aren't they } ! *G*

Might want to put back the not isnull again. I think that is for text. the NZ works better on dates.

Have a great evening! Chat tomorrow!

Mary )

 

[jksb]

Good Morning and Here We Go Again!

I changed it and now get &quot;Invalid No. Arguments&quot;, the Private Sub line highlighted yellow. Here is the code:

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

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

txtCharterNo = DLookup(&quot;[OldCharterNo]&quot;, &quot;Inventory Description&quot;, &quot;[CharterNo] = '&quot; & strCharterNo & &quot;'&quot

If (Not IsNull(txtCharterNo, &quot;&quot) <> &quot;&quot; Then Me.OldCharterNo = txtCharterNo
End If

End Sub


I did look up NZ and it said as long as I change the Allow Zero Length in the table it would allow zero length strings, and I did so, but it didn't make a difference. Or I misunderstood the Help in Access

Thank you..hope you have a nice hot cup of java going!

Jackie, almost snowed-in in Michigan!

 

[Telsa]

Hi Jackie!

Oh! That last line (the IF statement)....

IF (not isnull(txtCharterNo)) then Me.OldCharterNo = txtCharterNo

Since we change the type of NULL function, its parameters change too (which is why you get that error).

Mary )

(We got some snow here in Idaho, but not much.. mostly in mountains. Keep Warm!!)

 

[jksb]

Hi!

If I leave the &quot;End If&quot; in, I get a compile error &quot;End If Without Block If&quot;; help tells me I need to tell it to do something?

If I delete it, I can run the code, but get that Null error when I enter a number that has a blank number to look up.

Thanks!
Jackie

 

[Telsa]

Hey Jackie!

Here is how IF statements work...

If you press ENTER after typing up to THEN

then you must use an end IF to complete the statement.

However, if you put it all on one line, you don't need the END IF.

So this line doesn't need the END IF because it is all on one line.

IF (not isnull(txtCharterNo)) THEN Me.OldCharterNo = txtCharterNo

But if we were to do it this way

IF (not isnull(txtCharterNo)) THEN
Me.OldCharterNo = txtCharterNo
END IF

We need that END IF.


On that NULL issue, here is what mine looks like that I have used with text data...

If (Not IsNull(varDiscPart)) Then Me.DiscontinuedPart = varDiscPart

Yours is similar so I'm not sure why it is not working since we are dealing with text fields in the table.

I'll have to chew on this one for a bit...

Mary )

 

[jksb]

Thanks! I also noticed that when I entered the number that the OCN is blank, instead of giving me a blank field, the OCN stays with the new number...in otherwords, it's not changing back, but treating it as a new entry.

Thank you...I am learning *a lot*...I am able to read these codes a bit now! It's just getting them to do what *I* want them to do!

Jackie

 

[jksb]

I did a keyword search and found a thread that said only the variable can be Null...so, if the variable is CharterNo, and the Nulls are in OldCharterNo, is that where the problem may be? Am I understanding this right?

Jackie