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]

Have you ever used Dlookup? I found very useful in autofilling the fields based on the data in one field.

Start in the field's AfterUpdate. You'll need to code the rest in the form's module for that field.

You set up a variable to equal the form's field first (something like strID = me.ID# (your field name)).

Then for each field you want to autofill,

Fieldname1 = dlookup("[Field]","Table", "ID in Table = '" & strid & "'&quot

I'm assuming that ID is a text field. The names I have placed here are to guide you to input the field and table names you are capturing the data from.

If I have totally confused you, checked this site at Allen Browne's

http://odyssey.apana.org.au/~abrowne/casu-07.html

HTH

Mary )

 

[jksb]

Okay..so I do this in the properties/text box of each field in the form?

Tx! Jackie

 

[Telsa]

Actually, you will edit the module for the form and field.

While in the design mode of your form, place your cursor on the field you will use to trigger the update (ID#). Open its property dialog box. Scroll down to the AfterUpdate field in the properties dialog box.

Click in the box and 3 dots (elispses) will appear. Click on it. A Choose Builder dialog box will appear. Select Code Builder.

The Visual Basic IDE will open and take you to the field's AfterUpdate procedure.

Private Sub ID#_AfterUpdate()

Here you will add in the code to say

Fieldname1 = dlookup("[Field]","Table", "ID in Table = '" & strid & "'&quot


Best to create a variable to capture the [Field] info for each field you want to duplicate. So it would look like this instead

txtField = dlookup("[Field]","Table", "ID in Table = '" & strid & "'&quot

Then you need to add the next line... it will prevent errors from popping up for empty fields...

If (Not IsNull(txtField)) Then
Me.Fieldname = txtField
End if

Then just repeat the above lines in the same procedure and change the field names.


HTH

Mary )

 

[jksb]

Hi again!

"NotIsNull" is coming up as a "subfunction not defined"

Also, should I be putting in the name of the textfield where you put txtField in the code?

Thank you, btw...I now feel like I am making progress!

Jackie

 

[Telsa]

It's Not ISNull ... two words.

txtField will be a variable you create that will hold the data captured in the DLookup function. Then you just copy what it into your field. You can call it anything you want. Just be sure to declare (DIM) it first!

And the type must match the field type... so if you have a text field you are capturing, then declare your variable as a String. If the field is Numeric, then the variable must be set as the type of Number (Integer usually). IF date, then Date...

Glad I can help! I have received LOTS of valuable help on this forum and want to repay what I have learned.

Mary )

 

[jksb]

Thanks! I've decided I need to read the VBA sections in the Unleashed book, and it's really helping. Will tackle this again tomorrow.

Jackie

 

[jksb]

Help! It doesn't like the trailing single quote that is recommended by both you and Allen Browne. This is what I have set up (per you)(btw, the expression is in one line normally)...

Private Sub Charter__AfterUpdate()

Dim charterNo As TextBox

charterNo = Dlookup]"[OldCharterNo]","InventoryDescription",CharterNo = '"&strid"'&quot

If (Not IsNull(charterNo)) Then
Me.OldCharter_ = charterNo
End If

End Sub

This is how Mr. Browne suggested:


Dim charterNo As TextBox
charterNo = DLookup("OldCharterNo","InventoryDescription",CharterNo = '"&[CharterNo}"'&quot


Now, that TSQ after the equal signs in both equations is being kicked out as a "compile error expected:expression".
I'm don't know what to do with it; help says to remove it, but then the expression still won't work.

Thanks!

BTW, what is "strid"? I can't find it what is is anywhere

Jackie

 

[Telsa]

StrID is simply a variable... name it anything you want. It is going to be the variable that contains the value captured from the form you will use to compare against your table.

Let's say your field is called "CharterID" then create a variable to capture that value such as strID or (looks like you are using an integer) intID.

Dim intID as Integer


Use a variable to capture the info from the Dlookup function.

Dim intCharter as Integer

Capture the value from your control on the form...


intID = Me.controlID

Now get the OldCharterNo from the table where the table's CharterNo field value matches the value in intID.


intcharterNo = Dlookup"[OldCharterNo]","InventoryDescription","[CharterNo] = " & intID)



You won't need single quotes if it is an integer.

Make sure there are spaces before and after the ampersand (&)... and watch those quotes! )

DOes that help?

Mary )

 

[jksb]

Thanks! CharterNo is in text. I have it as a primary key,
and when I figured out how to keep it from rounding up, it wouldn't let me switch to integer. That's fine..I think I can live with that.

So, Will I then do this?

textID=Me.ControlID

txtcharterNo=Dlookup"[OldCharterNo]","InventoryDescription","[CharterNo]="&txtID)

or will I have to put those single quotes back in?

Also, I have several fields I need looked up; besides OldCharterNo I will need Description, VendorNo, Vendor, Manufacturer. Can I make a single program, or do I need to do one for each field? And how do I tell it to put the info automatically in the right field on my form, or will it do so automatically?

Thanks again!

Jackie

 

[jksb]

AGH! I just closed my forms and went to open them again...I am getting "Enter Parameter Value" with the subtitle "Inventory Description.Charter#". When I put a number in, it takes me to the forms..without their Charter#
Help!

Jackie

 

[jksb]

Okay, that one I figured out..I was changing my #s to No in
my table, and my forms couldn't find them. I changed them back, and it's okay now. How do I make a change like that so my forms will recognize it?

Thanks! I'm off in 10 mins; have a nice weekend.

Jackie

 

[Telsa]

I made a few corrections...

txtcharterNo=Dlookup"[OldCharterNo]","InventoryDescription","[CharterNo]= '" & txtID & "'&quot

Since you are dealing with a text field as an ID, then you need the single quotes in it. And don't forget to make sure there are spaces around the ampersand!

Watch your spelling! In your message, you have the variable called textID and in the function you have txtID!

Yes you will have to do same for each field. Just create a variable for each field and for the captured data from the table that you want to duplicate for your next record and repeat the above function changing the variable to the new names.

Chat next week!

Mary )

 

[jksb]

Good Morning! Hope you had a nice weekend.

Okay, I've got this setup:


Private Sub CharterNo_AfterUpdate()

Dim txtcharterNo As TextBox
txtcharterNo = DLookup("[OldCharterNo]", "Inventory Description", "[CharterNo]='" & CharterNo & "'&quot

End Sub

And get this error:

Object variable or With block variable not set

When I debug it highlights the entire strand.

Help! It's Monday!

Thanks!

Jackie

 

[Telsa]

Dim txtChartNO as String

It's a simple string not a control on the form. Textboxes are controls on the form. DOes that help?

Mary )

 

[jksb]

Okay...now, before I go further, I would like the CharterNo to be a combo box..should I set it up as that (I saw it in the drop down box)? Or add it later once I get this working? Thanks!
Jackie

 

[Telsa]

Sure, go ahead and make the drop down box.

Set the Row Source Type and Row Source to the table you will capture your CharterNo from in the Properties of your drop down box on the form.

Then the data captured will populate transaction table and then the other fields will automatically fill in, depending which event (AFterupdate? Lost Focus?) of the CharterNo you have the Dlookup function coded in. Have all the fields coded you want to autofill in that one procedure.

After you have done the first autofill field successfully, you will see all you need to do is repeat the DLookup code and change the field names and the variable names (don't forget to Dim more variables for each field you want to autofill!).

Happy Monday!!

Mary )

 

[jksb]

I just know that after I get one working, I'll "get It" but help! Now I'm getting invalid use of Null for this (it highlights yellow the string of commands.

Thanks AGAIN! Jackie


Private Sub CharterNo_AfterUpdate()

Dim txtCharterNo As String

txtCharterNo = DLookup("[OldCharterNo]", "Inventory Description", "[CharterNo]='" & CharterNo & "'&quot
If (Not IsNull(txtCharterNo)) Then
Me.OldCharterNo = txtCharterNo
End If

End Sub

 

[Telsa]

Don't you just hate that! Nulls are a real pain....

Okay,

Try this

Instead of the

If (Not isnull(txtCharterNo))

Use

If Nz(txtCharterNo, &quot;&quot <> &quot;&quot;

Mary )

 

[jksb]

Compile Error: Expected: Then or Go To


Your new command is in red. I copied below how I have it set up.

Does it want me to put something in the <>? If the
field is blank, I want it to stay blank (only about a dozen
of the entries have Old Charter Nos as we are switching to new ones).

Tx for working with this!
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;

End Sub