I have a form which is used for entering holiday requests.
It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??
I have inherited a buggy database thas has a simple piece of code that won't behave the way I think it should! The code should add a new record and have that new record appear on the list form (where the button On_Click event is called) making it available for user edditing. In 99% of cases though, the blank new record does not appear. The record is getting created, as i can see it in the table and have added a watch to the RecordCount property, it just doesn't display automatically. The existing workaround for users is to click a second button that invokes a simple one line function sub that performs only a Me.Requery. The user presses that up to two or three times before the new blank record finally appears.
One solution I read about, but hesitated in trying due to the unprofessional 'flash' that occurs, was to close the form and the re-open it...anyone experienced this or can comeup with a neat workaround?
The code
Private Sub CmdAddRule_Click()
Dim db As AO.Database, intnextseq As Integer Dim TestThis As String Set db = CurrentDb() Dim rstDao As DAO.Recordset Set rstDao = db.OpenRecordset("select * from stblAllocRules order by intRUSequence desc;", dbOpenDynaset) If rstDao.RecordCount > 0 Then intnextseq = rstDao!intRUSequence + 1 Else intnextseq = 1 End If With rstDao .AddNew !AutRU_id = 0 !intRUSequence = intnextseq !bytAppendBack = 1 !bytUpdateBack = 0 !bytDeleteBack = 0 .Update .Close End With
Set rstDao = Nothing Set db = Nothing
Me.Requery Me.Repaint 'I have used this staement in addition to and instead of the Me.Requery to no effect Me.Recordsource = Me.RecordSource 'I have used this statement also in addition to the above to and in isolation, to no effect. End Sub
I'm trying to get a form to display a pick list so I can choose a staff name from the whole database and the form will go to that record. In the after update of my combobox I have:
Private Sub STAFF_ID_AfterUpdate() Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone rst.FindFirst "LNAME='" & STAFF_ID.Column(1) & "'" If rst.NoMatch Then MsgBox "The selected record can not be displayed because it is filtered out. " _ & "To display this record, you must first turn off record filtering.", _ vbInformation Else Me.Bookmark = rst.Bookmark End If Set rst = Nothing End Sub and this works OK except when I try to make it pick on 2 columns I can't get it to work. E.g. if I select Joe Smith it just goes to the first Smith. I thought if I used AND on the findfirst line and put in the first name it would work. What am I doing wrong?
I also need to make it so if I type a letter the list will go to that part of the list but I'm not sure how to do this.
I have a master table with several pick list columns. One field in particuliar has 3 options(fed from a separate table): a b c But, the user will have a need to select more than just 'a' for example. they will need to select 'a' and 'b', or 'b' and 'c'....etc
I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.
result = sum(cost1) - calc1 selected record value
I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
well i am trying to make a very simple program that would help me learn the capitals of each country. yes it is a weird thing to do but i just it might help. anyway i started doing it using access and made a table with three columns primary key number, country and capital. I started making a form and im tryin to make it so the program would randomly pick a country and i would have to choose from a list box the capital and i would have a command button which when i click will tell me if i am right or not and if i am not then it would say the right capital. How should i do this? p.s ino the basics of using the code builder help much appreciated.
I am trying to issue multiple INSERT statements in a row - but it seems that only the first succeeds. If I put a 1-second delay between attempts, suddenly they all succeed (so I know the statements themselves are all valid, it's not a data issue).
These are all being issued in a loop from the same thread, so as far as I can see unless the data is being inserted asynchronously, I can't see any problem. If it is being done asynchronously - how are we supposed to know one INSERT succeeded and thus we can issue the next one?
Anyone have any idea why this might happen? I don't want to leave 'magic delays' in the code!
Hello I'm helping a friend with an access project and the Referential Integrity check is failing for some reason unknown to me. I recreated the entire database from scratch and added all relationships before adding any data and then added in the data. The tables giving me problems are the following:
Orders Table: OrderID - Primary Key Autonumber, Format:1000, Indexed: Yes(No Dupes) CustID - Number, foreign key to a customers table, 1-many relationship SalesID - Number, foreign key to a sales table, 1-many relationship ShippingID - Number, foreign key to a shipping table, 1-many relationship 3 different date fields and a price field
This table is filled with data.
Order Details Table: ItemID - Primary Key Autonumber, Indexed: Yes(no dupes) OrderID - Number, Dec. Places: Auto, Required: Yes, Indexed: Yes(dupes OK) RingID - foreign key to rings table, 1-many relationship StoneID - foreign key to gemstones table, 1-many relationship Ring Size - number
There is no data in the order details table.
The Orders to Order Details is a 1 to Many relationship with ref integrity and cascading updates and deletes enforced.
When I try to manually type in a record I get the "You cannot add or change a record because a related record is required in table 'Orders'" even though I added a lookup list box listing all the OrderIDs from the Order table. I know I'm typing in a valid OrderID number, but it's not taking it and throwing this error.
I have created a FE/BE database which is operating on a network. There is one FE for each person (approx 150) all accessing 1 BE. There are at most, maybe 10 people using it at once. The database has been working well for about 2 months.
My problem is not with data corruption (touch-wood!), but with a complex form.
The Form is split into a Header/Footer, with a Detail section containing a subform. The main form filters records that appear on the subform. The subform is continous, with several conditional formatting cells per record.
Most of the time it works fine, but occasionally, the main form opens, but the subform fails to load/open. All I see is a white page with the main form header displaying correctly along the top 1/3rd of the screen. No error message is displayed. If I print the page using a Report (The subform and Report use the same query) the page prints perfectly with all records showing.
I have seen this problem on several different workstations, only to try again later to see it working (having closed the db, logging off then back on again)
I am at a loss with this one. Is it a problem with Access or the network? I am unable to find anything similar to this problem on the forum. Can anyone shed any light on it?
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between] FROM [Main Table] WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
I can see which records are missing from the table I want to import to but this query picks up no records: SELECT [Field1] FROM Table_Linked WHERE [Field1] NOT IN (SELECT [Field1] FROM TableA);
Yet, when I run them separately, all fields are displayed.
Am I doing something wrong with the NOT IN part of the sttement that access doesn't like? If you run this in SQL server it works fine.
I have a query which picks picks up all records from a table. One field however seems to randomly not pick up data from certain records even though the data is in the table.
This has been puzzling me for some time now, does anyone have any ideas / suggestions?
I have a query with a list of Customer SSNs and Claim Rep, I want to randomly pick 5 Customer SSNs for each of the Claim Rep. I can't figure out on how to do that, I was able to create an SQL code to just randomly pick the customer SSN. I don't know anything about Visual Basic codes, but if that is the only solution, please go ahead post it. Thank you very much in advanced.
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005")) ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code: CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
I am using an unbound object frame to display an .png image file which is set in vba.
The image is an excel chart saved as a .png
The problem I have however is that the image I save from Excel is great quality, and is pretty tack sharp, but when I display the same file in the unbound object frame in access, it is not nearly as sharp.
I am setting the picture property of the control as:
ubImageUserChart = "c: empmyChart.png"
It displays alright, but is just a bit fuzzy - still quite legible, but it is a complex graph with a lot going on - has regressions and formulas etc on the graph, and they need to be very clear.
BTW - the unbound frame is the same size as the Excel chart which gets saved as a .png file. If I tile the images (Access unbound frame and original file in picassa preview) side by side - they are identical - size, orientation etc.
It is not practical for me to try and do the chart natively in access as it is way to complex. I am using access vba to drive an excel session to do all the statistical yack work and chart rendering, then displaying a png image of the resulting chart in an unbound object frame in access.
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
I'm incrementing a counter every time I click on a combobox by using the on click event and this code:
Code: POcount = POcount + 1
I have 5 fields on my form: GWS1, GWS2, GWS3, GWS4 and GWS5.I want to store the value of the combobox when clicked into the correct GWS field. For example: If I click the combobox 3 times it would store the value of the combobox after the 1st click in GWS1, after the 2nd click in GWS2 after the 3rd click in GWS3.I tried
i have a list box that fills based on the following code.....
Dim strSQL As String strSQL = "SELECT Products from [Client ProdVend] " & _ "Where Client_Account_Name = '" & Me.Client_Account_Name & "'" Me.List91.RowSource = strSQL Dim strSQL As String strSQL = "SELECT Products from [Client ProdVend] " & _ "Where Client_Account_Name = '" & Me.Client_Account_Name & "'" Me.List91.RowSource = strSQL
There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.
The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....
I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
I have hit a roadblock trying to send data to excel. I have created a table of Arrays where each record has an ArrayID, a record ID, an X unit and a Y unit. I want to be able to pick an ArrayID from a form and send the corresponding X and Y values to excel as part of a Linest calculation.
I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.
Code:
Private Sub lblEdit_Click() DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog End Sub
I have one table (500 Club) with two fields (ID) (Name) i would like to randomly pick a record from the table and display the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.
