I am currently modifying a form on my access database. More specifically the on click event of a button on the form. The form is based on a Table that only has 3 fiels, ID, GoingToCal, and Location. ID is the autonumber. GoingToCal is a multiselect list box on my form. Location is just where the user is submitting the entry from. The table is simply a way to track what went out at what facility.
The issue that I am having is 2 part. The first error I have doesn't create a runtime error, it simply doens't work. What I am attempting to do is update a recordset of the items selected in the list box. The items are in another table, and I have based my recordset on a query. When I run my code I am expecting first for the specific record to be found(I think works), then update a yes no box to yes. The Yes No field is called StillOut. It is a part of the query. However it doesn't update the Yes No box to Yes, nor does it seem to update any of the boxes. So I am confused as to what I am doing wrong that the field for that record isn't updating.
My Second issue, which I know there can be a few possible ways to work around I just can't make some of them happen. I am using the FindFirst for how I narrow down my recordset to just the one record I want to update. It doesn't seem to like "," with it. I have tried many times when setting the OpenRecordset to the specific record using SQL and a mix of SQL and the Query but no matter how hard I try I can never return any results. It constantly says too few parameters. That is why I am using the FindFirst command.
how to make the record update the Yes No box and how to specifically change just the records that have been selected in the form and a way to select those records.
If Me.GoingToCal.ItemsSelected.Count <> 0 Then
For Each oItem In Me.GoingToCal.ItemsSelected
If iCount = 0 Then
I have a code that I am currently using with a button to move to the next record. I just want it to loop by itself. I am currently having to press the Update button for it to move to the next record.
Private Sub UpdateAllComments_Click() Dim memoContent As String memoContent = Me.Remarks1 If Not Me.Recordset.EOF Then Me.Recordset.MoveNext Me.Recordset.EOF
I have a database application that i intend to split with the table part on a server and the forms part on each users desktop. It will be used by approx 10 users. While the probability of different users accessing and updating the same form is small, the possibility exists.
Is there a way i can prevent any weird multiple update stuff happening? I'm using Access 2003
I have created a form and subform using form wizard where users could enter the data of a new order.In the form, there are some expressions where it does a computation of the fees that the company earned for each order. This is a percentage of the gross income.The subform expression formula updates the fee amount and net income automatically when the gross income is entered and fee percentage entered.
Is there a way to update the fee amount and net income which the expressions derived into a data field in a certain table?I actually need these information to be in the table too, as data as they are required by other users.Or is there a better way to do it?I am mainly using wizards and don't know how to use any SQL or VBA.
i have developed an access database and working on interface through forms & reports .i needed that a user must have to click save button to update the data and i have found that without an unbound control its hard to achieve functionality .so i want to know step by step how to update records using unbound controls and a save button .
I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder] So if we look at washing dishes as a task then the steps are as follows:
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink , 1, 12.1 512 Turn on tap, 2, 12.2 205 Put in detergent, 3, 12.3 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5 etc
If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 4, 12.4 435 Turn off tap, 5, 12.5
Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?
StepID, StepDescription, StepOrder, StepNumber 202 Put plug in the sink, 1, 12.1 512 Turn on tap, 2, 12.2 210 Place dishes in sink, 3, 12.3 435 Turn off tap, 4, 12.4
My form is setup with Continuous view, with data feeding from a table. The structure is straighforward (excel style). AgentID, workHrs, holidayPaid, holidayUnpaid, sickHrs etc. The structure is not normalized, and I know the risk. This is basically only for human interpretation, not really a structure that will cause instability or patched up working of the DB.
I have the continuous form view, so it can be viewed in one single display, I have placed a Totals at the footer, which is populated by nothing but a simple = Sum(columnName). This works great in most of the computers, except one. My boss's computer. Which is where it actually needs to work. I am baffled, because I have tested it on almost every single computer in my office, everyone's work. Just his computer is playing up. I am unable to pin point the problem. The status bar keeps going "Calculating..." but even after giving it a solid 5 minute it does not seem to work !
The front end has been saved in a accde format and distributed to 20+ users. They have all saved this onto there computers and this is used as an application.The users complete a form on a weekly basis on the application and then save. This then saves into the back end.For some reason I have noticed that a few users submission randomly do not save. For the life of me I cannot understand why.All users have saved them into the back end in the past but every now and then submission for users do not save.
need help with looping to get a total for a specified time block. It needs to look at a time block and count the number of entries for a hour before to an hour after 7 am, 11am and 3pm on given 12 shifts. The list of shifts come from another sheet with the date and the time of the end of the shift.
I have the following which gives me a total for the 12 shift but now need to loop for the times given:
For rowz = 13 To 20 endtime = Sheets(sheetname).Cells(rowz, 1).Value begintime = endtime - 12 / 24 If IsDate(endtime) = False Or endtime > Now() Or endtime = Empty Then GoTo Exit End If
SQLstatement = "SELECT count(Field1), count(Field2),count(Field3), count(Field4), count(Field5), count(Field6)" & _ " FROM dbo.table WHERE [Date] Between '" & begintime & "' And '" & endtime & "' " rs.Open SQLstatement, , adOpenDynamic
If rs.EOF = False And IsNull(rs(0)) = False Then fieldcount= rs(0) + rs(1) + rs(2) + rs(3) + rs(4) + rs(5) Else fieldcount = "N/A" GoTo Exit End If
Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.
Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.
Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.
Do I need another loop here? Example Do Until EOF RST1?
Here is the code:
Dim rst As DAO.RecordSet Dim DIRName As String Dim folder As String Dim Dir As String Dim strnewname As String Dim stroldname As String Dim Pause As Boolean Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim blnExcelOpen As Boolean Dim strMacro As String Dim mysheetpath As String Dim rst1 As DAO.RecordSet Dim objdb As Database
' Processing each officer and creating a directory MkDir DIRName
Sleep 10 FileCopy stroldname, strnewname 'Name stroldname As strnewname Sleep 10 mysheetpath = strnewname blnExcelOpen = IsExcelRunning() If (blnExcelOpen) Then Set xlApp = GetObject(, "Excel.Application") Else Set xlApp = CreateObject("Excel.Application") End If
Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False) Set xlSheet = xlBook.Worksheets("Sheet1")
'Transfer the data to Excel xlSheet.Range("A2:r10000").ClearContents
Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!
I have a series of fields on a form. Each field name is a number 1 thru 32. I use a loop to set field properties
Param = 1 For z = 1 To 32 If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If Param = Param + 1 Next z
I also have on the same form a series of field whose names are alphabets "a" thur "t"
I would also like to loop thru these fields to do the same thing. Is there any way to do this? I have tried the following code but it doesn't work. I'm thinking I need to set up temp fields to reassign each of these fields a number then loop thru it. I havn't tried it yet. but I'm wondering if there is a way to loop through the alphabet.
Param = "A"
For z = "A" To "t"
If (Me(Me.ParamB & Me.Param).Visible = True) Then Me(Me![ParamB] & Me!Param).Visible = False Me(Me!ParamL & Me!Param).Visible = False Me(Me!ParamE & Me!Param).Visible = False Me(Me!ParamT & Me!Param) = Null End If
Hi, not sure looping is the correct word so i will try to explain what i am trying to do.
i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.
is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.
I am trying to add another record to a table. Pid and nsn are the keys. the table has 70 unique pid's. I need to add another nsn to each of the pid's. I tried the following code but no go:
Set db = CurrentDb Set rst1 = db.OpenRecordset("tblHandReceipt") Set rst2 = db.OpenRecordset("tblHandReceipt")
rst1.MoveFirst Do Until rst1.EOF rst2.AddNew rst2!PID = rst1!PID rst2!NSN = Forms!frmparts!NSN rst2!ISSUEDSOURCE = Forms!frmparts!subfrmEndItemParts.Form!cbxENDITEM 'Add like above line for each field rst2.Update rst2.MoveNext rst1.MoveNext Loop rst1.Close rst2.Close
I have a form with an UPDATE button. There is a subfrm on this form that lists all my units. I want the update button to loop through all the records on the subfrm and update the WOSD based on the information below. Is this possible?
Private Sub cmdUpdateWOSD_Click()
Call UpdateWOSDChange
End Sub
Private Sub UpdateWOSDChange()
Dim AddColor As Boolean Dim intNumDays As Integer
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.PreFin
Case "BR17", "BR28", "WH06" AddColor = True Case Else AddColor = False End Select
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.DrStyle
Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23" If AddColor Then intNumDays = 7 Else intNumDays = 6 End If
Case Else If AddColor Then intNumDays = 7 Else intNumDays = 4 End If End Select
Well, this should be easy... But I'm doing something wrong and can't find anything in the forum as an example.....I have a Form for creating an invoice billing for segments of time spent on a project. This form (frmInvoice) has a subform(frmHoursTimeInvoice) This subform has 2 checkboxes, one for "Billed" (HoursBilled) and one to include unbilled time into the invoice total (InvoiceHours)So... When you open the form to create an invoice the subform shows only unbilled hours.... as you ckeck the boxes (InvoiceHours) it adds the time to the invoice and calculates the total... So far so good........ Now the problem. I attached a "Close" command button to test this code....Private Sub cmdCloseSave_Click()On Error GoTo Err_cmdCloseSave_ClickIf [frmHoursTimeInvoice].Form![InvoiceHours] = True Then[frmHoursTimeInvoice].Form![HoursBilled] = TrueElseIf [frmHoursTimeInvoice].Form![InvoiceHours] = False Then[frmHoursTimeInvoice].Form![HoursBilled] = FalseEnd If End If DoCmd.CloseExit_cmdCloseSave_Click: Exit SubErr_cmdCloseSave_Click: MsgBox Err.Description Resume Exit_cmdCloseSave_Click End SubNow the problem is after it marks the first record (HoursBilled) True... it stops executing the code. I know it needs a loop here.... But I cannot find an example that works!! It needs to loop through all of the records in the subform and check the correct boxes True and leave the others False.So whats the best method? AND the correct syntax! Would a "For each... (something).... Next" work here??Thanks!PS.... Sorry for the lengthy explanation :)
I am building a Inventory Management Application for Tyre Shop. I have SaleMainTbl and SaleDetailTbl both used for preparing daily sale summary. I have Mainform based on SaleMainTbl with TxnDate and Total Amount (Sale) and the TxnDate is in one to many relation with SaleDetailTbl. FormSaleDetail is multiple row(continuous) form that makes billwise summary of each day having -TxnDate--BillNo--ItemSold--Company--Qunatity--Rate--Amount fields. I have inserted this form in FormSaleMainTbl.
So FormSaleMainTbl is Main form and FormSaleDetail is subform. TxnDate in FormSaleDetailTbl is automatically taken from SaleMainForm. I have further added text boxes in Main Form to show company wise sale for each day for which there is a query build one for each company that takes the currently loaded date from FormSaleDetail and calculates the Sale (Sum) of each brand (Company) of Tyres. All these objects are working very fine. However I have to close the MainForm and reopen it for result of query to appear in the appropriate text box in Main form.
Is there way to do this as soon as record is entered or at least at the end of completing the entry of each days sale transactions without closing the form. So the gist of the problem is realtime display of query result in text box on a form or updating the form screen immediately on updating any record or at the most after completing the updating of form but without having to close the form.
I am trying to edit data in a table that has 30 fields.The fields names are W1,W2,- up to W30, and their value would correspond to a control on a form I have declared the controls in an array, so i would really like to use some sort of loop
Is There any way to use a loop to refer to fields to update them rather than having to use rst.edit ![W1]=Ctl(1) ![W2]=Ctl(2) ![W3]=Ctl(3) etc rst.update
What would the correct syntax be using a for next loop for i = 1 to 30
rst.edit !["W" & i]=Ctl(i) - this doesnt work rst.update
I've got a problem. The database-user can search through the database and the results are displayed in a subform. There's a button attachted to that subform and when that button is pressed, the files, which are linked to the found records and saved outside the database, have to be copied to an other folder. So how do I make I loop for copying those files?
I've got the following code behind the button (but it doesn't work):
Private Sub Command56_Click() Dim rst As Recordset Dim strBnaam As String
Set rst = CurrentDb.OpenRecordset("[Forms]![frmZoekVast]![qryZoekVast subform]") With rst Do While Not (rst.EOF) .MoveFirst strBnaam = rst!Tpon FileCopy "\NLFS006A110154$Stage BidteamSysteemTeksten" & strBnaam & ".doc", "C:TempOfferteTraject" & strBnaam & ".doc" .MoveNext Loop End With End Sub
I’m in the process of developing a holiday property availability form for displaying the available and free dates for a particular property in a particular period (usually 35 days). The main form allows the selection of the start and end dates of the period using a Calendar control and then completes text boxes with the days of the week for each day of the period.
The next step for the user will be to click on a property button to run a select query to find the booking records that are present for the availability period being looked at. Each booking record has the start date and the number of days booked together with the property name and other fields.
I have the select query results as a subform and can access fields on the first record using Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate MsgBox Mydate However, I need to be able to access each record on the subform to be able to colour each text box for each day the property is booked so the user can see visually which days are booked for that period.
I’m struggling to find out how access the subform recordset to be able to loop through the records and pick off the days that are booked. I’ve tried defining a recordset but VB rejects the dim setting with Can't find project or library. Can somebody point me in the right direction?
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================ 'Public Procedures
Public Sub UpdateQuestion() Dim UpdateAnswer As Byte Dim db1 As DAO.Database Dim rst, R As DAO.Recordset Dim volval As String Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _ "work note ready to update and complete Press" & vbCrLf & _ "YES to update and complete OR " & vbCrLf & "NO just " & _ "to update and close", vbQuestion + vbYesNo + _ vbDefaultButton1, "Update and Complete") If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else Set db1 = CurrentDb Set R = Me.Form.RecordsetClone 'create a recordset of the data R.MoveFirst 'got to start somewhere Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels") rst.Index = "PrimaryKey" rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID] rst.Edit volval = rst![Volume] rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume] If rst![Volume] < 0 Then MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle rst![Volume] = volval rst.Update rst.Close Exit Sub End If rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status] rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate] rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber] rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID] rst.Update rst.Close
There has got to be a better way to do what I want to do. Here is the situation:
I have a weekly forecast (up to 14 weeks worth) that I want to apply to the inventory that I have on hand to generate figure out how many 'days' of forecast that I have on hand. Eg. I have 7 units on hand, next week's forecast is 7 units therefore I have 7 days of inventory.
Here is how I am doing this:
SQL = "SELECT SKU, " SQL = SQL & "IIF([ON_HAND]<=[W1 FORECAST], [OVERSTK_POT]/([W1 FORECAST]/7), " weeks = "[W1 FORECAST]" days = 7 end_brackets = ")" For n = 2 To 14 weeks = weeks & "+[W" & n & " FORECAST]" days = days + 7 end_brackets = end_brackets & ")" SQL = SQL & "IIF([ON_HAND]<=(" & weeks & "), [ON_HAND]/((" & weeks & ")/" & days & "), " Next n SQL = SQL & "105" & end_brackets & " AS OVERSTOCK_DAYS, "
I have 2 issues with this code. The first is that 12 nested iif statements is all that will be allowed in this loop before the query becomes too complex. The second is that this cannot possibly be the best method to accomplish this. I'm hoping someone might know a better method.
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format 2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS) 3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?) 4) Use an AND statment with an if statment (how to do this with correct syntax)
Code: sub loopandfix() on error goto errorhandler: strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc.This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?
Code: Dim rst As DAO.Recordset ' recordset for category data ' open the recordset for categories Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset ' loop through the rows in the recordset rst.MoveFirst Do Until rst.EOF
