Changing Categories In Access Inventory Template?
Feb 12, 2013I'm using access 2013 and I like the inventory desktop template but cannot find out how to edit categories, or location.
View RepliesI'm using access 2013 and I like the inventory desktop template but cannot find out how to edit categories, or location.
View RepliesI've got a continuous form that displays my massive 3,000+ inventory, with the option to filter down to specific categories. Some categories have 5 items, others have hundreds. The design I have used makes it look really tacky once I filter down to one of the categories that has 5 items, because there is no scroll bar.
What I've tried using is:
Code:
If Me.RecordsetClone.RecordCount < 10 Then
Me.Scrollbars = 2 'vertical only
Else
Me.Scrollbars = 2
End If
But it still doesn't display correctly (nobar.png). This is probably my biggest headache right now, and every "solution" I've found, doesn't work.
Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
How would I go about doing this?
View 6 Replies View RelatedSuppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another.Cubes can be either Red or Green, Spheres can be either Blue or Yellow.
I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )
e.g. -- I have several boxes totaling:
Cubes
Red 30
Green 40
Spheres
Blue 10
Yellow 20
My goal is to produce three reports.The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)
The First Report would look like this
Target % - Actual % - Target # - Actual #
Cubes 60% 70% 60 70
Spheres 40% 30% 40 30
The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions.
I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the order that they appear in the query results.
Some specifics:
Tables:
tblFreq
FreqID (PK, Number, Range 1-7)
Frequency (text)
tblResp
RespID (PK,Number, Range 1-5)
Response (text)
tblResults
ResultID (PK,AN)
FreqID_FK
RespID_FK
Query:
TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID
SELECT tblFreq.Frequency
FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK
GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID
ORDER BY tblFreq.FreqID
PIVOT tblResp.Response;
[code]...
Which I suppose is alphabetically ordered.I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.
I am creating a database relates to hospital medical equipment using Access 2003. Using VBA code, I want to generate an AutoNumber for each Equipment falling under different Equipment and different Hospital.
Ex: DGG-ECG-001
DGG-ECG-002
DGG-XRY-001
WBH-ECG-001
WBH-XRY-001
WBH-XRY-002 and so on.....
Where DGG & WBH are Hospital Codes (PK in tblHospitalCodes) ; ECG & XRY are Equipment Codes (PK in tblEquipmentCodes) ; last 3 digit denotes the AutoNumber.Both the Hospital & the Equipment select using combo box then generates the AutoNumber Using command Button.
Hi there,
I am not the best in Access, I will admit. But need a function to open up a MS Outlook template (.oft file) within Access which is hosted on an internal server. Anyone help with quick steps on how to do this, It would be greatly apprciated.
Hello All,
I thought i would ask this question an how to import data from excel into access.
I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.
The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.
The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.
Any thoughts
Alastair
UPDATE
**************************************Resolved Thanks to Pat Hartman *************************
i have a form and was wondering if it was possible to take some of the fields and drop them into a template word document from inside access. so as to create a button that when clicked opens up the template and transferes the wanted form data.
any idears how to automate this process would be greatly appreciated
thanks in advance john
Hello all..
I have an HTML template with about 15 TAGS built in to it..such as:
[SKU]
[MITVALUE_TITLE]
[MITVALUE_ACCESSORY1]
[MITVALUE_ACCESSORY2]
[MITVALUE_ACCESSORY3]
[MITVALUE_ACCESSORY4]
[MITVALUE_ACCESSORY5]
[MITVALUE_ACCESSORY6]
[MITVALUE_ACCESSORY7]
etc. etc. etc...
Now I would like to somehow populate those fields from the tables that i have.. (The all have a common "related" field)
example:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<style type="text/css">
<!--
.style10 {color: #FF0000; font-size: 12px; }
-->
</style>
</head>
<body>
<table width="100%" height="100%" border="0">
<tr>
<th height="23" colspan="2" scope="col">ITEM INCLUDES:</th>
<th width="30%" height="100%" rowspan="4" scope="col"><a href="[MITVALUE_PHOTO5]" target="_blank"><img src="[MITVALUE_PHOTO5]" width=266 height=200 border="6" align="right"></a></th>
</tr>
<tr>
<th width="73%" height="23" colspan="2" scope="col"><p align="center">[MITVALUE_ITEM]</p></th>
</tr>
<tr>
<th width="10%" height="23" scope="col"><div align="left"></div></th>
<th width="38%" scope="col"><div align="left"><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="0D4587"> </font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY1]<br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY2]</font></font></b></font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif"><br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY3]</font></font></b></font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif"><br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY4]</font></font></b></font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif"><br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY5]</font></font></b></font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif"><br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY6]</font></font></b></font><font color="#000099" size="2" face="Arial, Helvetica, sans-serif"><br>
</font><font size="2" face="Arial"><b><font color="DADADA" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000099" size="2" face="Arial, Helvetica, sans-serif">[MITVALUE_ACCESSORY7]</font></font></b></font></font></b></font></div></th>
</tr>
<tr>
<th height="23" colspan="2" scope="col"><span class="style10">THIS ITEM ONLY INCLUDES ITEMS LISTED ABOVE </span></th>
</tr>
</table>
</body>
</html>
................................................
I would like those fields to be put in automatically.. wether they are in a memo field within a table or a field within a query ... it really doesn't matter.... Anyone have any ideas?
Regards,
OvAdoggvO
I am using a template that I downloaded off of MicrosoftOffice.com. I really like the setup but I need to make a few modifications to it, but the modifications I want to make I can not do due to it being a web-based(I think). I am a newbie to access and I am slowly learning about macros, vba codes, formatting, and what not.
View 5 Replies View RelatedHello, I am here back in the forum because would like some help to define a code.
Meloncolly has already helped me but I think that I have mixed up two pieces of code and cannot find the solution.
I need to export my access data to a document in excel.
I will select the recorset of the data to export, using a combo box on a form named MENU.
The excel file is a template named MASTER. Before the recorset is copied, I need to make a copy of the template via code. The copy will be the document into which copy the data. Once the data is copies, I would like an input box asking users if they would like to save the new excel file and be able to name it with the name they will type in the input box.
My last problem is that the database is used by 20 users and what happens if are all exporting to the template? Will this automatically name itselft, MASTER1, MASTER 2, etc?
This is the code that I am using and tried to put together. It does copy the data into master 1 but leaves the template opened. It also tells me that there is something missing.
THanks:
Private Sub ExportToExcel_Click()
Dim myid
Dim obj As Object
Set obj = GetObject("C: estMaster.xls")
'Dim mypath
obj.Application.Visible = True
obj.Windows(1).Visible = True
obj.Application.ScreenUpdating = False
myid = Me.[MyCombo]
'grab the three field values from the table
Dim mySSN, myFirstname, myLname
mySSN = DLookup("[WESSN]", "[MASTER]", "[id]=" & myid)
myFirstname = DLookup("[WEFN]", "[MASTER]", "[ID]=" & myid)
myLname = DLookup("[WELN]", "[MASTER]", "[ID]=" & myid)
'open excel and the required file
Dim appXL3 As Excel.Application
Dim blnStartXL3 As Boolean
On Error Resume Next
' Check if Excel is already running
Set appXL3 = GetObject(, "Excel.Application")
If appXL3 Is Nothing Then
blnStartXL3 = True
'Else
' We have to start Excel ourselves
Set appXL3 = CreateObject("Excel.Application")
If appXL3 Is Nothing Then
MsgBox "Can't start Excel", vbExclamation
GoTo exit_handler
End If
End If
With appXL3
'.Visible = True
On Error GoTo Err_Handler
'open the excel file
'mypath = "C: estMaster.xls"
'.Workbooks.Open mypath
.ActiveWorkbook.SaveAs "c: estMaster1.xls"
.Sheets("Data").Select
'enter variable values into cells
.Range("B6") = mySSN
.Range("B3") = myFirstname
.Range("B5") = myLname
'do some other stuff
'save the workbook
.ActiveWorkbook.Save
'close it too
.ActiveWorkbook.Close
.ActiveWorkbook.Close
'exit and tidy up
exit_handler:
On Error Resume Next
If blnStartXL3 = True Then
'We must quit Excel
appXL3.Quit
End If
Set appXL3 = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation
DoCmd.SetWarnings True
Resume exit_handler
MsgBox "The tables have been successfully exported to " & txtExportFile & "."
Exit Sub
End With
End Sub
What I have now is an excel template (with ~12 worksheets) that many regional offices use to enter in some lease data, from which the excel sheet creates a rental schedule and does a whole ton of calculations on that data. Some are NPV calculations, some are yields etc etc.Eventually, I would like to:
1) Enable users to fill out one of these templates, and save the data to the database (Just the inputs? All the data? My reading suggests just the inputs)
2) Use the database to produce one of these templates for any lease in our system (shouldn't be hard, from what I've read)
3) Sum up calculations from this template for many records (eg. if a tenant has many leases, what is the NPV of all of those leases, or what is the total NPV for all tenants)
I have the inputs (from Access) I will have no problem using them in the excel version, but does it make sense to use Access given that I may need to somehow be switching back and forth to get the info I want for my various reports? I am very comfortable writing macros in VBA for excel, so if that's the solution, that is no problem. I assume what I need for #1 and #2 is a macro to arrange the inputs from the excel sheet into a format that can easily be dropped into access tables and vice versa.
I am creating a db for my work place that records incidents that take place. I am a novice at Access but I have made different tables, queries, forms and reports and they all work.
I was wondering if there was a way to extract data that a user enters into a form and then use that to populate a word template *without* coding using VBA? I am really a beginner and not confident with VBA at all. I have read the forum and that seems to be an option (but would rather something else if possible!)
I'm doing a project for my work. I created a few reports in Access. Some of these reports are simple graphic bars. How can I insert these reports into a word document template?
View 2 Replies View RelatedHello,
I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.
I don't know much about access but I was hoping one of you could point me in the right direction.
I used the Contacts Database Template to import a comma delimited txt file of contacts data. The template included a number of tables, queries, forms & reports which I did not require. I deleted them leaving only a Table entitled 'Addressbk'. I added an Entry Form using the standard access tools and this works fine with the table. However, on running the access database I get the following error message; 'The form named "Main" is mis-spelled or refers to a form that doesn't exist. My only form is labelled 'Entry Form', so it would appear that one of the forms I deleted must have been named 'Main'. If one ignores the error message the database & entry form function perfectly.
View 3 Replies View RelatedHow much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.
Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?
I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
How to open a pre filled excel template in read and write mode from msaccess vba and insert data from msaccess tables into specific columns in excel.
Rename the tabs in excel sheet based on a specific column in the access data.
Each row in the table will go to a seperate tab in excel.
Save the excel template after populating the necessary data into different tabs.
Hi,
I'm very new to Access, having used it for the first time today :)
I've got a database to write for a media database as a learning example and I've run into my first real problem...
I've got a table for users and I've got to allow users to be able to own one or more media types, e.g. CD, DVD, video, record, etc. Of these types, there are sub-categories for most of them. For example, a DVD may be a horror film, action film or music video, and a CD may be hip-hop, rock or blues, etc.
I thought I could have a reference data table containing a category ID and category value, together with a reference data category table which simply listed the categories. This would work fine if a user could only have one type of media but of course they can have several. So, I thought about having a "composite category" instead which would contain a category, whether it contained a sub-category and a sub-category ID. I'm trying to get my head round this and wondered if this must surely be a fairly common issue to solve. Does anyone know of any pointers or any examples I could do with looking at?
Sorry if I haven't explained it too well :o
Okay here is my silly little issue, my boss decided to add sub categories, just when I get the database set-up.
Table 1
Category
Category_ID
Category_Name
Table 2
Sub_Category
SUb_Category_Name
Table 3
Contacts
Company
Contact
Adress
Ect Ect
I would like to be able to have each contact grouped by category, then sub category..but I am not sure how to get the relationship to work.
Hi guys,
I am implementing a new database (Access 2000) for my company.
It contains contact details of a subscription base.
Four tables are present MEMBER, ORGANISATION, EVENTS and CATEGORY.
My problem is; people attend events, and i want to record this in the database. Each event has an event ID field, and an event description field.
When recording the events which people attend, i could just use the event ID field, but problem is that people can obviously attend more than one event..
Also;
People belong to a certain category
Again i have a category ID field, but people can belong to more than one category.
Any ideas on how to record this info??
Hi,
I am working on a database to list all the inkjet cartridges we sell. Within the printer table, I want to list which category they are on our website. For example, Epson C20 Printer would be in Ink & Toner > Epson > Inkjet > Stylus C & CX Series.
I what some way to enter these in a number of fields, say Cat1, Cat2 etc. I havent a cluw what would be the best way to do this? Has anyone got any ideas? I am thinking about some kind of drill down but wouldnt know where to start on this. Any help would be gratefully received?
Cheers
J