Hi,
On our main menu table, I only want to be able to add one record.
On the form that I have linked to this table, should you press the tab key to go through all the items / buttons, it opens a blank record screen. However, I do not want this. Any ideas?
Thanks in advance
Lee
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have two tables
tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10
tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15
One product can have up to 10 locations in the tbl_StockLoc
I need to print the following report (up to LOC10)
Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15
I need to export/print every single record from table as a pdf file (one record one pdf file). how to do this.
I'm working on Access 2000.
Any Single line of code available?
View 2 Replies View RelatedI am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
:eek: I have a large table with 1 damn corrupted record, the database can't be repaird, compact, I can't use the copy ,method since the table is over 9000 record sets.
I tried creating a new table and appending the non-corrupted record into it with no success, it is keep locking on me.
Have you guys tried anything else that works for this situation???
Thanks a bucnh
I have tables Account (PK acctnum) and Orders (linked via acctnum to Account). There are multiple orders per account that need to be billed individually. When creating an invoice I need to add the Account to the Invoice table and the Orders to the InvoiceDetail table. I use 2 queries to accomplish this. The first one though adds multiple records to the invoice table (because of the one to many relationship). I need to keep that so I don't bill anyone with no orders.
INSERT INTO Invoices ( InvoiceDate, AcctNum )
SELECT DISTINCT Date() AS InvoiceDate, Account.AcctNum
FROM Account INNER JOIN [Order] ON Account.AcctNum = Order.AcctNum
WHERE (((Order.OrderNum)=[Forms]![Account]![Order].[Form]![OrderNum]) AND ((Account.BillingCycle)="on discharge") AND ((Order.EndDate) Is Not Null) AND ((Order.Closed)=Yes) AND ((Order.PatientName)=[Forms]![Account]![Order].[Form]![PatientName]));
Is there any way to get it to only add one record? I've tried limiting it with several parameters to no avail.
I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
View 5 Replies View RelatedI would like to be able to copy a single record in a table and then paste this record a pre-defined number of times 10,20,30 depending on requirements. The reason I need to do this is that I want to set up a number of identical records that can easily be amended into unique records. This will vastly reduce the amount of work and time spent entering records individually.
The only way that I have been able to achieve this is to copy and transfer the master record into Excel, and then copy the row and paste it into a range of cells. Copy the range of cells back from Excel and then paste this range back into the Access table.
This works but is long winded; what I would like to know is is there a simply way of achieving this.
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number
GO2 1 123
GO2 2 456
GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 ..
GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append.
2. Getting Serial Numbers from several records to save on to a single record.
I have 2 tables and a query to show the details of both tables on a form for an attendance database. The idea is that whenever someone is absent they have a record created.
tbl[Staff] has the following fields StaffID,Name,Team,Job Title
tbl[Absence] has the following fields StaffID, abStartDate,abEndDate,abIssue
I can set up the query to find the member of staff by name but I want to be able to use this form to add new records to the Absence table for each individual. eg I use the query to search for "Joe Bloggs" and the form shows the details from staff table and then use the form to add details to the Absence table.
Any help would be greatly appreciated
I have 2 tables and a query to show the details of both tables on a form for an attendance database. The idea is that whenever someone is absent they have a record created.
tbl[Staff] has the following fields StaffID,Name,Team,Job Title
tbl[Absence] has the following fields StaffID, abStartDate,abEndDate,abIssue
I can set up the query to find the member of staff by name but I want to be able to use this form to add new records to the Absence table for each individual. eg I use the query to search for "Joe Bloggs" and the form shows the details from staff table and then use the form to add details to the Absence table.
Any help would be greatly appreciated
Hi,
I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.
I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.
However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.
So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.
stDocName = "approveLease"
DoCmd.OpenQuery stDocName, acNormal, acEdit, "[LeaseId]=" & Me.buildingCombo.Column(0)
Any ideas on how to actually accomplish this?
I need to set up a preferences type form. What's the best way to accomplish this. Can I store this data in memory as a variable?
Here's what I need:
Customer Name, CustomerID.
I have been reluctant to put it in a table because this is the only data that I need.
Plx lead me down the best path.
Is it possible to create text box for search for records if I use SINGLE FORM?
i try but without effect :(
pls see my database (I want to use for record "Title")
Thanks
hello. can i refer to a single record in a detail section, and not all of them? for example if i use something like this (after update in field1):
if me.field1 = 0 then
me.field2.enabled = false
any single field1 equal to zero will disable ALL of the field2's. i would like this to work within each record independently.
I am trying to make calculations at a single record level. I have many products and I want to select only 6 of them, which I just found out that I can do it by their SupplierID. then I have a query that finds the amount of pcs that I have for each product. The aim is to determine the final weight of this 6 products. So to start I need to find the weight of each individual product by multiplying the number of pieces (from the query) by the weight of a single piece. Obviously each product weights differently, and at the end I need to add all the weights of the individual products to make the total weight of the 6 products.
don't know how to approach the problem of selecting a single record. And also I don't know what would be best to do it in a query, a form or a report.
I've done some looking into how to validate in Access and I found how to do it for an entire field but I was wondering if it's possible to validate a single record in a field.
View 6 Replies View RelatedI have a form NoWorkOrder, i have attached a command button to Print Report. The report is named No Work Order. When I'm viewing that a record in Form View and click the command button i want only that record to print out onto the report.
I'm using Ms Access 2010.
So I have a list of jobs and each job is subject to change from the original plan.
Each change needs to be documented and dated. So what I want is a form where the site can be selected and a note written. When this is saved, a record of the current time would also be needed.
The form aspect of this seems fairly straight forward to me. I just cant visualise where the 'notes' will be stored.
I would like to be able to place a button on my invoice form that will allow me to e-mail the individual invoice to the person being billed (the e-mail address is included on the form). I created a macro that works to send a PDF file of all of the records and I am having trouble figuring out how to send just the current record. I would also like to have the e-mail address automatically populated based on the e-mail address on the current record.
View 3 Replies View RelatedHi all. Using Access 2003 I am trying to figure out how to print and collate a report and an existing pdf file that is opened based on a field in the record. I figure that it probably has to do with looping through the records but I am not very good at that.
So I have a form with a sub form that opens records based on a combo box. I then have a report (a checklist sheet) that prints 1 page per record. I would like to print a pdf file associated to that record before it prints the next record.
Here are the names of the objects being used:
tblMoveDoc
frmMoveDoc
subfrmMoveDoc
rptCheckList
Any help would be appreciated.
Thank you,
Matthew
This particular database generally looks at reporting individual records as opposed to summary reports. So for a particular data record i have the following data [x1][x2][x3][x4][x5][y1][y2][y3][y4][y5]
I need to chart (scatter) these figures as paired x,y variables for display on a feedback report such as
x1,y1
x2,y2
x3,y3
x4,y4
x5,y5
For some reason i simply can't wrap my head around haw to make this happen.
Many Thanks in advance for any help.
Cheers
ndeans
Hello Query Expert
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
How would you write this in SQL?
Thanks so much.
Hi fellow Access battlers!
I've searched high and low on this forum on a step by step on how to do this and everyone's answer is either vague and assumes everyone is an access wizard, or really complex and codey and assumes every one is an access wizard!
I have a "lead sheet database" and I need to add a button on the form which emails just the contents of that current record. My company still handwrites these lead sheets and faxes them to consultants so I'm trying to bring them into the 21st century! An uphill struggle. I'm a code and VB newb by the way.
I've tried sending the form to a report as other posts suggest, but I get an error message about there being too many fields. So I'm stuck. All I need is for each form record to be emailed to a different consultant each day.
(I've had to rar then zip the database file due to size restrictions. I couldn't get the files size down enough by just zipping.)
Thankyou!