Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)
Code:
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As Database, rs As Recordset, rs1 As Recordset2
Dim Criteria As String
Dim strAns1 As String
Dim strAns2 As String
Dim strCount1 As String
Dim strAns3 As String
The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.
What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.
First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.
Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
I have a back end database that a number of people are working with using an Excel add-in that imports and edits records saved in the backend. Occasionally, when a user will update a record at first it saves to the backend but then an hour or two later, the changes made disappear. We have validated that after the user saves the changes, those changes are present in the backend of the database, but for some reason those changes disappear after a period of time.
I am trying to create a form in which users can save new supplier data to a database. I am using two insert statements which insert similar data in to two similar tables. The insert statements appear to have no problems and no errors are produced upon execution yet the new data doesn't save into the tables. Here's the code;
Code: Private Sub Command14_Click() Dim sqlstr As String Dim dbs As Database
Is it possible to have sequal loops of some kinda? The basic setup of my DB is a table named "EC Faculty 2005", field name "Status", and the status field can be either "Approved" "Disapproved" and "In Process". There is another field called "Term Start Date" which holds months. I have been trying to create a SQL or some kind of query that will go through each month Jan, Feb, Mar, and so on, and count the number of Approved, disapproved, and in process for each month. This is what I have so far. Code:SELECT (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Approved') AS Appr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Disapproved') AS Disappr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='In Process') AS In Proc FROM [EC Faculty 2005]; That gives me a total count of the 3 statuses. Is there a way to loop this to count each status for each month? I hope thats not too confusing.
I am attempting to create a form for the automated printing of a series of reports. The form is tied to a query that captures Territory ID, Sales Rep ID, # of Reps in that Territory, Total # of Territories. Once completed, the code behind the form should: 1)Print the cover page report (Salesrep Sales Analysis – Cover); 2)For each of the territories, print a report for each sales rep (Salesrep Sales Analysis - A Rep) and once all the reps for that territory have been printed, print a summary report for that territory (Salesrep Sales Analysis - A Territory; 3)Print a combined report for all sales reps (Salesrep Sales Analysis - ALL by Month); and finally 4)Print the final page report (Salesrep Sales Analysis - Territory Totals).
I created the following code to accomplish that, but the results are in error: 1)The first rep for the first territory is being repeated (doesn’t seem to move off that first record at the correct time); 2)The final rep for the first territory is being skipped, and the first summary report is printed; 3)The second territory ends 1 rep short and the code moves into the 3rd territory without printing the summary for the 2nd territory; 4)The 1st rep of the 3rd territory prints, then the territory summary prints, then the 3rd territory continues, again stopping short of the final rep before moving to the 4th territory; 5)The two reps of the 4th territory (only 2 for that one) print, then the summary report for the 5th territory prints without printing the 4th territory summary or the report for the single rep for the 5th territory; 6)All three of the reps for the 6th territory print, then the summary for the 7th territory prints; 7)Three of the four reps for the 7th territory print, then I get an error message “You can’t go to the specified record” which ends the routine before the summary for that 7th territory and the last two reports are printed.
Private Sub Form_Open(Cancel As Integer) DoCmd.Maximize
' Set Variables for Rep Count, Territory Count, Page Number and Date/Time TotalReps = Me.CountOfSO_Rep RemainingReps = TotalReps Me.RepCount = RemainingReps TotalTerritories = Me.CountOfTerritory RemainingTerritories = TotalTerritories Me.TerritoryCount = RemainingTerritories PageNumber = 2 Me.PageNumber = PageNumber Me.myTime = Now()
' Turn off action/warning messages DoCmd.SetWarnings False
' Loop through all the Territories - 01, 02, 04, 05, 06, 08, 09 Do While RemainingTerritories >= 1
' Loop through all Reps for the Current Territory Do While RemainingReps >= 1 DoCmd.OpenReport "Salesrep Sales Analysis - A Rep", acViewNormal DoCmd.GoToRecord , , acNext RemainingReps = RemainingReps - 1 Me.RepCount = RemainingReps Me.PageNumber = PageNumber + 1 Me.Repaint Loop ' End of Rep Loop
' Print Territory Totals Report for the Final Territory DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
' Print Totals Report for All Reps Combined DoCmd.OpenReport "Salesrep Sales Analysis - ALL by Month", acViewNormal
' Print Totals by Territory Report - Final Report Page DoCmd.OpenReport "Salesrep Sales Analysis - Territory Totals", acViewNormal
' Notify User that All Reports Have Been Printed Beep MsgBox "Salesrep Sales Analysis Reports have been sent to the printer.", vbOKOnly, ""
End Sub
I’ve spent a couple of days looking at this, trying different things, and am not getting satisfactory results. Can anyone find what I’m doing wrong here? Thanks very much for any help you can provide!
I have successfully paged through a recordset on my page using the .recordCount, etc. methods, the only thing is I have 2 independent recordsets looping on the page, which when there was not paging involved this worked fine, however now I need to span the results of BOTH of these across pages, I just cannot seem to figure out how! I have one set of records paging fine, but the other either messes the page up, or shows on the first page of results and no more of the other recordset, etc.... so yeah that is basically the problem I am hitting, so I was just wondering is there any examples or ways you could tell me to do this??
Any help that can be offerered would be very much appreciated ).
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
I have a form "release_details" having fields date, version, cksum ,comments,labels, and is link to a table, it has a button "mai"l ,on clicking this button a new form is open which has a button "send mail" on clicking this a mail is sent and pops a message "mail sent".I need when "send mail" is click it should also save the fields of form "release_details" to the table.
Hello, I'm new to this whole microsoft access program, so I'm learning from scratch. My question is I can't figure out how to save the database, so that I can go back into it later. This is a tutorial that I'm working off of in my online class, so once I exit out and go back in, all the info that I inputed is gone.
Is there a simple way to make sure all fields on a form are filled before the data is saved to the table? I looked into using thew IF is Null Then but for 30 fields that is alot of code. Does anyone know a easier way?
We recently switched over to A2003. As far as my db everything went ok except for one thing.
I have a continuous form that we enter data into. When you open a new record the form only displays one empty row. Because we enter tons of rows that come from an AS400 system I made a button on the top of the form that switches from continuous form view to datasheet view.
Private Sub Label59_Click() Me.[Part #].SetFocus DoCmd.RunCommand acCmdSubformDatasheet End Sub
Within datasheet view I am able to paste many rows at once. However, after the change to A2003 it still "appears" to work. Many rows are pasted in but if you exit the db and come back to the record all of the data is lost and you are back to one blank row in continuous form view.
When a record is added to the continuous form each record is assigned an autonumber primary key. I think that this is where it is failing. Within Access help is the following under "Cutting And Copying Data", "Copy or move records or data from multiple fields to a datasheet": To paste the data as new records at the end of the datasheet, click Paste Append on the Edit menu.
I think this is what I need to do but I don't allow my users access to any menus so I create command buttons or auto running code in place of menu items that they need.
Can anyone think of some code to run a Paste Append of these records or of any other method in which I might restore the multiple paste funtionality I had with A2K?
Got a quick question, is there a way to have a field in a subform update a field on the primary form's table? In other words, enter the data once and it stores it twice, on the subform table and the primary form table.
My boss wants to add his own job numbers, so I made the JobID field a number field that he can enter (instead of autonumber, which is obviously preferable). So, in order to prevent him from entering a duplicate primary key (JobID), I have the following code in the BeforeUpdate event:
Code: Dim dbRoofing As DAO.DatabaseDim rcdJobs As DAO.RecordsetDim intJobID As IntegerSet dbRoofing = CurrentDbSet rcdJobs = dbRoofing.OpenRecordset("tblJobs")intJobID = Me!JobIDrcdJobs.FindFirst "JobID=" & intJobIDIf rcdJobs.NoMatch = False ThenMsgBox "This job number already exists.", vbOKOnly, "Invalid Job Number"Me.JobID = ""Exit SubEnd If with the 'Me.JobID = ""' in there to set JobID back to nothing so he can't save a duplicate. However, Access won't let me do this, and when a duplicate is added, all sorts of errors pop up. How do I prevent him from saving a duplicate job number, or more generally, how do I prevent certain data from being saved based on other data?
I have a form that stores its data in a table called Participants. Within that table is a field called Payment Due Date. On the form, this field is populated via the following:
Private Sub PaymentDueDate_Exit(Cancel As Integer) If Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] < 30 Then Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] > 30 Then Me![PaymentDueDate] = (Me![StartDate] + 14)
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] < 30 Then Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] > 30 Then Me![PaymentDueDate] = (Me![StartDate] + 30)
ElseIf Me![PaymentSchedule] = "Paid" Then Me![PaymentDueDate] = Me![StartDate]
End If End Sub
My problem is after the field is populated in the form, the data does not store in the table. Have I written something wrong? Any help would be appreciated. Thanks
come up with the below code to create PDF documents based on a query. It has been working great but I have a change that I need to make and can't figure it out. The code below creates all the documents in one folder "serverD$DocumentsInvoices" but I need it to create a separate folder for each PDF so that the file (Invoice1234.pdf) will be in the folder (serverD$DocumentsInvoicesInvoice1234Invoic e1234.pdf). That folder does not exist, so I need to create it and then save the PDF into it.
Code:
Dim db As DAO.Database Dim rst As DAO.Recordset Dim qdf As QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("Invoice_Report") Set rst = qdf.OpenRecordset
I am trying to find out what VBA Code I should use for a way to save a main table name: Master_Template on a certain day of the week (weekly) to happen automatically on close of the database - of course on that certain day of the week.
1. open outlook mail; 2. copy the email details into the database; 3. save the attachment into a E: drive location; 4. move the email to another folder.
1,2 and 4 were working fine until I tries to save the attachment and got stuck on the SaveAs line.
Dim Ola As Outlook.Application Dim Nsp As Outlook.NameSpace Dim pf, Inbox, ib, newdest As Outlook.MAPIFolder Dim msg As Outlook.MailItem Dim Atts As Outlook.Attachments
Ok. So I have learned my lesson on saving images into an Access DB. Now I'm using VBA (another good learning experince) to save the images back to a folder. I have a code working that will save the images but I need it to also rename the images after saving.
The section "MyFile = OldFile" needs to reference the rsChild Attachment file name...
Quote:
Private Sub Command859_Click() Dim db As DAO.Database Dim rsParent As DAO.Recordset2 Dim rsChild As DAO.Recordset2
I always feel as though I'm asking a stupid question but here goes. I am making a database with sub tables and sub forms, and working from "Access 2003 inside out". When I open table properties and enter a subdata sheet name and fill in child and master links everything seems fine until I try to save it. I open the table properties window again and the sub data sheet option has returned to "auto". Am I doing something stupid or is there a bug in my system?
Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.
SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4 FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year])) GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);
In my database I have a button on a form which leads to a subform popping up. On the subform popping up, I display data from the first form. However, unless I first save the original form and then go to the subform, the data isn't passed on. How can I make the button both open up the subform and save the form so that the data is passed on?
