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 am creating a database to store employee data, its divided among 3 tables, storing :Employment data,Personal data and Payment data, the 3 tables are related using a 1-1 relationship between the common field ID in each. I`ve created a form through which all data needed for the fields in each table is entered,while entering the data its possible to view the records, however, when i close the form and check the tables, the data is going to the respective tables,however,after i open the table and then go back to the form, its not possible to view the records in the form, they`re still in the table but i cant view them through the form, i can still add new records using the form, but i`m not seeing the old records anymore. Anyone, help?
View 5 Replies View RelatedI am creating a database to store employee data, its divided among 3 tables, storing :Employment data,Personal data and Payment data, the 3 tables are related using a 1-1 relationship between the common field ID in each. I`ve created a form through which all data needed for the fields in each table is entered,while entering the data its possible to view the records, however, when i close the form and check the tables, the data is going to the respective tables,however,after i open the table and then go back to the form, its not possible to view the records in the form, they`re still in the table but i cant view them through the form, i can still add new records using the form, but i`m not seeing the old records anymore. Anyone, help?
View 1 Replies View RelatedI 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 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.
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.
Hi folks,
I am struggling here with something that I am sure is simple but for the life of me can’t crack it. :confused:
I have a table and a subsequent query which shows golfers lowest score on each hole throughout the season. I want to add up the total of each hole by individual membership number and display it as a total for each row
I have attached a sample db with a sample query called 'eclectic latest working'. If you look at this query, I want to have the total of every row.
If you look at my form, the total I have at the moment isn’t correct, it is adding up the whole columns (I think)
If anyone can point me in the right direction, I would be most grateful !!
Thanks for looking
Regards
Steve
I have a form with two textbox, when I type something in textbox, the second textbox is filled with database query "SELECT MAX...", this works in one PC but doesnt work in other...
Why ?
all PCs is Windows XP with Office 97 and 2000.
thx
What would trigger an error to occur if there has been no changes to a DB.
My error # is 2427 (You entered an expression that has no value).
This error occurs when I click on a command button to open a report. When I debug, it sends me to an IF statement that I have loaded in the On Format of the report.
This worked perfectly fine before - the If statement is simple, if a value is true, then it changes a box to bold and if the value is false, the box in the report remains the same weight.
I am not understanding why it is saying that I have entered an expression with no value when the IF statement reads both the true and the false of a chkbox and adjusts the box accordingly.
Can someone explain why this is occuring? Im litteraly stuck.
I have to tables.
one dummy and one main. The have the same fields.
the dummy is filled by a form.
i want to see where they are diffrent. But the query shows all the dummys records. I just want it to show the record that doesnt exists in the main table.
SELECT DISTINCT AttendenceDummy.MeetingCode, AttendenceDummy.EmployeeCode
FROM Attendance INNER JOIN AttendenceDummy ON Attendance.MeetingCode = AttendenceDummy.MeetingCode
WHERE (((AttendenceDummy.EmployeeCode)<>Exists (select Attendance.EmployeeCode from Attendance )));
I just posted something about the calendar2k. Does anyone know where I can find that calendar add on? I know there are others out there but this one was awesome. It was 2 little buttons that dealt with start date and end date. You would just change the OnClick, so when the person clicked it or double-clicked it, it would enter the date in the correct control for you.
View 3 Replies View RelatedI'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 tab control with buttons nested inside of it calling to open a form - but when I go to test the buttons, they do not open the form that the wizard selected. I get the error message: invalid outside procedure. If i copy the button into the main portion of the form outside of the tab control - it seems to work. Any ideas would help. Thanks again!
View 1 Replies View RelatedThis error appears when a preview or print button is used. The form has been opened with this.....
DoCmd.OpenForm stDocName, , , , acFormAdd, acWindowNormal
But when I try to preview it gets the error.......
"Object doesnt support this property........."
Same error occurs when the form is opened with.....
DoCmd.OpenForm stDocName, , , , acFormReadOnly, acWindowNormal, stLinkCriteria
The preview button is this.........
Private Sub cmdPreviewRptWO_Click()
On Error GoTo Err_cmdPreviewRptWO_Click
Dim stDocName As String
stDocName = "rptWorkOrderCurrent"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreviewRptWO_Click:
Exit Sub
The really strange thing is I have 2 forms both coding the same..... One doesnt give the error!!
I think the error occurs when it is trying to save before previewing...But it has to save to view the report.
Anyone have the answer?? :confused:
I am trying to track down why i get a #div/0 error...
i have a form that is a sales invoice. It also has a subform with in where the items being sold are entered.
in the subform i have on the footer a Sum([Selling price])
so that i have a total in the subform..
then i pull that total into the mainform using =[subform2].Form![TOTAL]
it all works great.... but if some one buys multiple items and one price is left at 0.... i get that div/0 displayed on my totals..??
see the attached image for a visual of what i am talking about...
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=13727&stc=1&d=1148661307
Hi all
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??
Thanks all
Gareth
I am wondering why access does not follow its own code all of the time?
For example I have passworded forms following the tutorial on the Microsoft site. It mostly works when the participants fills in the wrong password we will get an error message box and when they cancel the password dialogue box it wont let the person enter the form - but not all the time. Sometimes if you press cancel it still opens the form anyway (without requiring the correct password).
This is similar with the module that I put in to prevent people from using the mousewheel to leave their record etc. Most times it works but sometimes it just isnt called on form load.
There is nothing wrong with any of these codes and when they work they work well but I cant seem to rely on access to do what is supposed to?
I have a table that is filled based on selections that a user makes in combo boxes on a form. I am having trouble trying to figure out the best way to prevent duplicates from being inserted in the table when I do the append query part.
Since the table is filled based on the various selections the user makes I dont have a primary key or unique field.
Can someone help me figure out the best way to do this append/update queries to prevent duplicates. Also if you have a strategy for setting up some sort of unique or primary key for fields that could all be the same yet in the table only 1 record will show, no duplicates.
I search a record with values that I am taken from form1 .
If i found the record i show it on the form2 else I add a new record to table with values that i am taken them form form.
this is my code:
Code: Dim stDocName As String Dim stLinkCriteria As String stDocName = "mainhazineh_m" DoCmd.OpenForm stDocName, , , stLinkCriteria Form_mainhazineh_m.mahp.Value = Form_mainform_m.Combo2.Value Form_mainhazineh_m.salp.Value = Form_mainform_m.Combo0.Value Form_mainhazineh_m.RecordsetClone.findfirst "[salp]= " & Form_mainform_m.Combo0.Value & " And [mahp]= " & Form_mainform_m.Combo2.Value & " And [shahrp]= '" & Form_mainform_m.Combo12.Value & "'" If Form_mainhazineh_m.RecordsetClone.RecordCount <> 0 And Form_mainhazineh_m.RecordsetClone.NoMatch = False Then Form_mainhazineh_m.RecordsetClone.edit Form_mainhazineh_m.RecordSelectors = True Form_mainhazineh_m.Bookmark = Form_mainhazineh_m.RecordsetClone.Bookmark Form_mainhazineh_m.RecordsetClone.Update Else Form_mainhazineh_m.RecordsetClone.AddNew Form_mainhazineh_m.mahp.Value = Form_mainform_m.Combo2.Value Form_mainhazineh_m.salp.Value = Form_mainform_m.Combo0.Value Form_mainhazineh_m.shahrp.Value = Form_mainform_m.Combo12.Value Form_mainhazineh_m.RecordsetClone.Update Form_mainhazineh_m.Bookmark = Form_mainhazineh_m.RecordsetClone.LastModified End If
my problem is that when i must add new record to db, it only change the first record of table with values that i make them red then add a new record that all fields of it is empty.
can anyone help me ?
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
Can this be done in a table within Access?
Thank You,
RRA23
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 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)
hi,
the following error message appears when trying to access my database. I can't seem to get into it at all, not even by trying to import the data tables over into a new database. the following message appears:
"The database has been converted from a prior version of Microsoft Access by using the DAO Compact Database method instead of Convert Database Command on the Tools Menu. This has left the database in a partially converted state. If you have a copy of the original format use the convert Database on the Tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database applications can't be recorded''..
the copy and the back up wont let me in either, this just suddenly happened.
Please help!
Thanks in advance
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
Code:
me."GWS & POcount" = combobox1
but that's invalid syntax.
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.
DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"
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.....