automatically fill in the values with search

[nyma96]

I have a table with date,item number,quantity,price.and so on...
Im using form to input new record and is there a way to automatically search the table and if I enter the item number, it will fill in the rest of the values with the previous record of the specific item number?
since the only thing that will change would be, most of the time, quantity and price? and also date and invoice number but I have that set up to repeat previous record until updated...

Thanks In Advance.

 

[WayneRyan]

nyma,

You can trigger this with a Command Button, or whatever.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * " & _
                            "From YourTable " & _
                            "Where ItemNumber = " & Me.ItemNumber & " " & _
                            "Order By InvoiceDate DESC")
If rst.EOF Then
   MsgBox("First Time Sale ... No Help")
Else
   ' Copy fields
   Me.SomeField = rst!SomeField
   Me.OtherField = rst!OtherField
End If

Wayne

 

[nyma96]

Thanks for the reply but...
how do I do this automatically? meaning I wouldn't have to create buttons or whatever...just input the item number and it would automatically search for last record of that particular item number if there was one and auto fill. or is this a bad idea?

Sorry Im a newb...just started access.The book I got doesnt help much with codes...

 

[WayneRyan]

nyma,

You can use the BeforeUpdate event of the ItemNumber field. After they
enter it, the code in the [Event Function] will run.

Just get your form in Design View, Right-click on the ItemNumber field
and select Properties. On the Event Tab, choose the BeforeUpdate,
[Event Function] and past the code in the VBA window.

Wayne

 

[nyma96]

Thank but Im getting errors...
run-time error "3464"
Data type mismatch in criteria expression...

 

[WayneRyan]

Nyma,

If ItemNumber is really a string:

Set rst = dbs.OpenRecordset("Select * " & _
                            "From YourTable " & _
                            "Where ItemNumber = '" & Me.ItemNumber & "' " & _
                            "Order By InvoiceDate DESC")

Wayne

 

[ScottGem]

I have a table with date,item number,quantity,price.and so on...
Im using form to input new record and is there a way to automatically search the table and if I enter the item number, it will fill in the rest of the values with the previous record of the specific item number?
since the only thing that will change would be, most of the time, quantity and price? and also date and invoice number but I have that set up to repeat previous record until updated...

Thanks In Advance.

This sounds like your database is not properly normalized. This sounds like an order entry database. If so, you need at least four tables here:

tblItems
ItemID (Primary Key Autonumber)
ItemDescription
Price

tblOrders
OrderID (PK Autonumber)
OrderDate
CustomerID (Foreign Key)

tblOrderDetails (this sounds like the table you are referring to)
OrderDetailsID (PK Autonumber)
OrderID (FK)
ItemID (FK)
Price
Quantity

You would then have a main form bound to the tblOrders and a subform bound to tblOrderDetails, linked on OrderID. On the subform you would have a combobox to select the ItemID. This combo would actually display the description but store the ID. You would then use the After Update event of combo to populate the Price control. This could be done in one of 2 ways. Either include the Price column in your combo query and use

Me!txtPrice = Me!cboItem.Column(2) or use a DLookup:

Me!txtPrice = DLookup("[Price]","tblitems","[ItemID] = " & Me!cboItem)

This is all illustrated in the Northwinds sample database with is an order entry sample.

 

[nyma96]

Thanks Scott...
but this is for my invoices that I get...not give out...
I get lots of stuff from different company as this is for a retail store..
what I am trying to do is input all of the invoices so I could track price changes and price difference between companies.

and Thanks Wayne...
the " ' " did the trick....

 

[ScottGem]

Thanks Scott...
but this is for my invoices that I get...not give out...
I get lots of stuff from different company as this is for a retail store..
what I am trying to do is input all of the invoices so I could track price changes and price difference between companies.

and Thanks Wayne...
the " ' " did the trick....

Ok, But that doesn't really change much. The only question is whether you want to track the invoices or just the price changes on the Items. You need at least the following:

tblVendors: This table has info about each company that supplies you with products.

tblItems: This table has info about each item you sell.

tblItemHistory: This table has the info from each Invoice:
ItemHistoryID (PK Autonumber)
ItemID (FK)
VendorID (FK)
InvoiceDate
Quantity
Price

You would would still only include ItemID as an FK in the History table.

 

[nyma96]

hmm...what I was gonna do was break up all the vendors by table...like
tblvendor1,tblvendor2,and so on...so your saying that isnt the way to go?
what I was thinking was if I had all that in one table, since what I get is not small amount every week, it would get crowded...and would fill up a table pretty quick...isnt there a limit on how much info I could have on a table?
and wouldnt it take longer to get info out later?

This is what I have so far...
tblVendor1
date
invoice number
item name
quantity per unit
unit
price per unit
each price
total price
adjusted price - adjustment for wrong prices...

we do get same products from different companies but couldnt I do a search across multiple tables?
we get about 400 items per week...so I thought it would be better to divide them by the vendors...

 

[ScottGem]

Separate tables is definitely not the way to go. You need to normalize your structure. Again, you should have several tables:

tblVendors
VendorID (PK Autonumber)
Vendor
other info about vendors

tblItems
itemID (PK autonumber)
Item
other info about items

tblInvoice
InvoiceID(PK Autonumber)
InvoiceDate
VendorID (FK)
other info specific to the invoice

tblinvoiceDetail
InvoiceDetailID
ItemID (FK)
Quantity
UnitPrice
Adjustment

This is assuming that invoices have multiple line items

Also, we do not store calculated values like total price since they can be calculated when needed (i.e. [Quantity]*[UnitPrice])

Also Date is a reserved word and shouldn't be used for objectnames. using spaces in object names is not recommended.

 

[nyma96]

ok...I understand about names and calculations...
but what do I do about ones without item number...?
the items that I get come with an item number and most of them have it, but not all.
I would like to use the item numbers that come with it since they're all different from company to company...
and with vendors...I dont really need the info on the vendors, thats why I didnt think about separating the table.

 

[ScottGem]

ok...I understand about names and calculations...
but what do I do about ones without item number...?
the items that I get come with an item number and most of them have it, but not all.
I would like to use the item numbers that come with it since they're all different from company to company...
and with vendors...I dont really need the info on the vendors, thats why I didnt think about separating the table.

In the example I gave you, I used an autonumber field as the PK for the ItemID. If you ALSO want to store the vendor's item number, then you can do that in a separate field (call VItemNo). This way it won't matter whether the vendor uses a number or not.

I'm confused as to why you wouldn't need vendor info. Don't you need to know info about what company supplied the item? Since you want to do analyze pricing history, don't you need vendor info to compare to? Even if its just a matter of a table with just the vendor name and an ID field, it should be a separate table.

 

[nyma96]

Thanks scott...gotta replan....
this is driving me nuts...

 

[ScottGem]

welcome to the squirrel club