I'm working in Access 97 and I've got a form that I want to use to create filters with.
There are a couple of things that I'm not clear about and I'd appreciate any suggestions that you can make:
1. The form seems to remember the last filter, so when I click on the Filter by form button it shows the last filter by default. I've included the following in the click event:
RunCommand (acCmdRemoveFilterSort)
Me.FilterOn = False
RunCommand (acCmdFilterByForm)
Surely either of the first two statements should remove the filter, shouldn't they? and if not then what are they for?
2. I can't get the 'OR' tabs to show at the bottom of the filter by form screen unless I apply the filter once and then click on the filter by form button again. Is there any way of forcing these tabs to appear?
I have a form "NewMetalF" that has three combo boxes. The first one is used to choose "Precious Metal" or "Base Metal". The second one shows all the metals based on the first combo box. When I open the form in form view alone the combo boxes work flawlessly. I run the form "NewMetalF" within another form "NewExternalPartF" by clicking a button "AddMetal". When the button is clicked "NewMetalF" opens. The first combo box is selected, and when the second combo box is clicked the error "Enter Paramater Value" "Forms!NewMetalF!cboPreciousOrBase".
I can't figure out what I have wrong in my code. I've searched all over the forums and the help files in Access. I'm sure it's something simple. Could someone please take a look?
I'm trying to get a sum of all defect quantities between a date range from a user form. Rather than summing all the defect quantities for a defect code, it's summing up the total quantites for each quantity. I'm sure that doesn't make sense so here's an example:
The domain is a query because the dates come from a different table than the defect quantities, so the query is pulling all the relevant data to be summed.
I have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK) 2. RPO_No : (Number field – Double, Duplicate OK ) The are project no. 3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate 2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO) 5421654 (RPO_NO) 641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate() If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!" Me.Undo Exit Sub Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?" STYLE = vbYesNo + vbInformation + vbDefaultButton2 TITLE = "!! ATTENTION !!" HELP = "TEST FILE" CTXT = 1000 Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
Else Me.MQE_NO = Me.CboRPO.Column(0) Me.RPO_No = Me.CboRPO.Column(1) Me.WORKSHEET_NO = Me.CboRPO.Column(2) Me.WORKORDER_NO = Me.CboRPO.Column(3) Me.WORK_DESC = Me.CboRPO.Column(4) Me.PL = Me.CboRPO.Column(5) Me.PipeLineKM = Me.CboRPO.Column(6) Me.DiaMeter = Me.CboRPO.Column(7) Me.PipeLength = Me.CboRPO.Column(8) Me.PipeLineArea = Me.CboRPO.Column(9) Me.P = Me.CboRPO.Column(10) Me.RPO_AMOUNT = Me.CboRPO.Column(12) Me.INV_AMOUNT = Me.CboRPO.Column(13) Me.Status = "WIP" Me.StatusID = 2 Me.CboStatus.SetFocus End If End If End Sub
I tried with following code also
'Dim cdn As String 'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'" 'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then 'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!" 'Me.Undo 'Exit Sub 'Else ‘do something ‘End if
Hey all, I have a strange problem that I can't seem to find the solution for...
At my work we have a number of computers running W2k with Office 97. We daily have to import excel spreadsheets into Access.
This is never usually a problem as obviously as soon as you click import the import spreadsheet wizard appears.
But on one particular machine it doesn't.
Basically on this computer Access will import the excel file, but won't bring up the wizard that lets you say what to import, whether the first row contains a header, the data type for each field etc, it just asks you to select what sheet you want, then imports it, no options.
Now the ironic thing is that usually this is not a problem as the file is still imported without any problems, but the other day we had a file that once imported was all messed up, all fields all over the place.
When done again on a different computer there were no problems so I'm assuming that this peculiar way of importing is to blame for messing up the data.
Does anyone know how I can resolve this? I have tried dragging the accwiz.dll over the regsvr32.exe and it says it's registered successfully, and I have checked that it is the correct version number, what am I missing?
It would make more sense if it just didn't import, then you could better understand how to fix it, but it's the fact that it does import, just not in the 'usual' way that bothers me. How can I make it bring up the proper import spreadsheet wizard?
I have a query where I'm selecting fields from two tables - Students and Attendance. I have selected option 2 in my Join Properties, i.e "Include ALL records from Students and only those from Attendance where the joined fields are equal". The tables are joined by StudentID. Either I'm misinterpreting what the join is supposed to be doing or it's not working. I need to see every record in Students regardless of whether or not they have a record in Attendance. I need to calculate absences and tardies. If there's a matching record in Attendance, I subtract the date absent from the total days. If there's no matching record in Attendance, I still need to calculate total days and show it. As it is now, if there's no matching record in Attendance, it doesn't do anything. How can I get it to do what the join says, "Show ALL records in Students as well as those that match in Attendance?
I am using the “count function” on an MS Access 2003 Form field. The count function seems to be working fine except it is not placing the result into the table field.
Does anybody know what I am doing wrong?
Can someone show me the proper format for using the count function used on a form field, where the result is to be placed in a table field?
Please Help!!!
Seti UNIVERSE http://www.setiuniverse.com/ All of the SETI (The SEARCH for EXTRA--TERRESTRIAL INTELLIGENCE) disciplines in one place. (Radio, Optical, Microwave, and Amateur SETI) Stop by Seti UNIVERSE and find out, what you didn't know about SETI.
I have a 'yes' check box and a 'no' check box. When the 'yes' check box is ticked, it enables a text box so I can enter a date and disables the 'no' box. Great, how it should work.
However, for some reason, whenever a date is entered into the text box when the 'yes' box is checked, every 'no' box afterwards is greyed/disabled out.
The thing is, it doesn't happen when no data is entered into the text box, when the 'yes' box is checked. So I can have the yes box checked, the no box will grey out as it should, and the text box will be enabled, as it should. But as soon as I enter a date, the above problem happens.
If that's not too confusion, can anyone help?
I've tried attached the database, but apparently it's too big, so i've posted the code underneath.
Cheers
Dan
EDIT: - i've just figured out that this problem is solved when I close the form and re-open it. But obviously I don't want to keep doing this every time I have to enter a date in a record.
I have a query which is filtering by date as criteria...but it won't filter right.There is a date field from a table in another query which has date/times in it.I have used Format([Field], "Short Date") in that query to display just the date.I am now doing another query which points to that query (It's a SQL Union query if that matters) and it has the following for the Criteria:Between "6/1/2007" And "6/30/2007"However, it is showing anything in the month of June for the years 2005, 2006 and 2007, instead of just 2007. Any ideas why it is doing this? It doesn't make sense to me that it will filter for the month/date but not the year.I also tried changing the format to Format([Field], "mm/dd/yyyy") and criteria of "06/01/2007", etc.
platform : access (adp) / sql server Problem : form filter not executing in subforms
When I goto one of my subforms I can activate the form filter. When executing the form filter no records show up, it just keeps showing my criteria. When disactivating the form filter I receive an error message telling that my view must be misspelled (it's about spaces). My view is called Vw_people , as a record source it works fine so it can't be misspelled.
Is the problem the adp connection or the subform? I don't know
I have a report "Work Order Details" that that is based on a query and opens along with a form that is used to set filters. Based on samples I have looked at and reading, I cannot figure out what is missing. When I select my item form one of 5 unbound combo box 's and click the "Set filter" button, it brings up a paramter box?? If I cancel out the box, I still don't get a result. Just for the heck of it, I filled in a name in the paramter box, still no luck. The report property has "Filter On" as YES. The close button, clear button, and drops downs operate perfectly.
Here is the On Click Event info from my "set filter" button:
Private Sub Command28_Click()
Dim strSQL As String, intCounter As Integer 'Build SQL String For intCounter = 1 To 5 If Me("Filter" & intCounter) <> "" Then strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And " End If Next
If strSQL <> "" Then 'Strip Last " And " strSQL = left(strSQL, (Len(strSQL) - 5)) 'Set the Filter property Reports![Work Order Details].Filter = strSQL Reports![Work Order Details].FilterOn = True End If
I have a navigation form which has two subforms on it (ie 2 tabs linking to different forms). I have got aq filter working for one of the forms but not on the other. This is using
If IsNull(Me.txtoperationscontract) Then MsgBox "Please type a contract number into the textbox!", vbInformation + vbOKOnly, "Incorrect Details" Else Me.OperationsSubform.Form.Filter = "[Contract]='" & Me.txtoperationscontract.Value & "' " Me.OperationsSubform.Form.FilterOn = True End If
The problem is that it does not work on subform B because the Contract field does not exist.
I recently made a new version of our database complete with forms, querys etc.. using Access 2010. It looked good and nice, but we encountered an unexpected problem. In all forms that contains memo-type fields quick filter is not working (there is no lists, checkboxes etc..). My users really like quick filters and are understandably irritated. I would understand if this disappearance would affect only memo-type fields, but it affects ALL the fields.
I also know that there are problems with memos in Access 2010. I have tried to make new forms, but every time I add memo fields quick filter stops functioning. I have also tried to change properties, options and what not, but to no use. In older versions of our forms this feature works just fine, but they were made in older version of Access (don't know which since oldest ones are from year 2003). Television
I have a query that filters two tables. 1) Status 2) Name
The status is 1,2,3,4,5 from an option group on my form.
In the query I have <>3 for the status, whereby I only want records that are status 1,2,3,& 5.
When I run the query with all names it works perfectly, however I am trying to run the query for only a select few names versus all of them.
The problem is I do not know how to select only a few names versus all of the names and make it work. When I try to add names i.e "Doe, John" my Status pulls all records including option 3.
Can anyone help? I do not even know where to start seaching.
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
Hi. I am running a form on top of a query with around 1500 records in it. The query mostly uses tables that are joined with referential integrity.
The form has buttons and conditional formatting.
It was fine until today, when suddenly the form has stopped loading properly. It looks all fragmented on the screen and I have to scroll down and scroll back up again for it to look right.
I have searched everywhere to find out what is causing this and how it can be fixed, but no luck yet. Can you help?
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber] FROM Cultureworkload GROUP BY Cultureworkload.Reasonfortesting PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
Okay, here's a question that should hopefully be simple. I have a form that I am setting the default value of two text boxes based on the form previous. Here's basically the rundown of what happensfrmForm1Input Month: FebruaryInput Year: 2012OK - Opens frmForm2 and then closes this one---------frmForm2Month_Default_Value: #Name?Year_Default_Value: 2012---------And this always happens for every month and every year. So could you tell me what could possibly be wrong with my "Month" default value? Here's the code...Private Sub Form_Load()Month_Query = [Forms]![frmStatementDialog]![Month]Year_Query = [Forms]![frmStatementDialog]![Year]Me.Month.DefaultValue = Month_QueryMe.Year.DefaultValue = Year_QueryEnd SubThanks!--Mike
I made a form a few years ago using Excel but it has become a major headache for anyone who wants to edit/update the content.
It's my responsibility to figure out how to fix this, but I don't really have any experience in form-creation! I'm not even sure what software to use... I've done some searching, and right now I'm thinking Access might have the tools that I need to help me complete this project (although I have no experience with the software at all right now).
I'm looking for some help on getting started.. so I will describe the project, and hopefully someone can answer a few of my questions.
This form has 3 sections: input, Q&A, and the summary. The input section is handled by the company before presenting the Q&A to a client. In the Q&A portion the client's name, company, and other personal information are pulled from the input section and placed mid-sentence in the questions. This is a personal touch we would like to keep when re-creating the form.
The Q&A portion also includes radio buttons, checkboxes, dropdown lists and input fields where the client can type out their answers sometimes. Here is an example of what the first 2 pages may look like:
PAGE 1: Question 1: Do you have a clearly defined purpose for your plan?
http://img425.imageshack.us/img425/3589/radio9sx.jpg If YES record the purpose below: _______________________________________________
http://img425.imageshack.us/img425/3589/radio9sx.jpg If NO, choose a sample purpose from the drop-down box to help get you started:
PAGE 2: Question 2: What selection critera did you use to select your service provider?
http://img71.imageshack.us/img71/2890/checked4na.jpg Investment options provided http://img200.imageshack.us/img200/5539/unchecked5rr.jpg Cost of service http://img200.imageshack.us/img200/5539/unchecked5rr.jpg Support tools proved http://img71.imageshack.us/img71/2890/checked4na.jpg Quality of service
Enter any other reasons below: _______________________________________________
One page per question.
The SUMMARY page will take client's answers and compact them into something more readable. The answers are listed one after another (no limit per page as with the questions). Also, the answers are to be displayed in sentence format. For example.. if a client checks off 3 items from a 5 item list, those three items should be listed in a sentence separated by commas. Here is an example summarizing the first 2 pages above:
Summary of Answers
1. Do you have a clearly defined purpose for your plan? A. YES. (description of plan previously entered by client)
2. What selection critera did you use to select your service provider? A. Investment options provided, and quality of service. Also (other resaons, provided by client)
This demonstrates the type of control that I need over the information. I managed to get Excel to do all these things.. but, like I said it is too complex to edit/update without accidently screwing things up. So lastly and most importantly... the final version of the form needs to be easy to edit!
So... does it look like Access the right program for the job? Maybe there are other programs out there that I should be looking at (something by Adobe maybe?)
But if you think Access can handle the job then do you have any tips to help me along my way? I'm not looking for specific step-by step instructions to help me build this of course.. I will figure most of it out by trial and error. But are there some major things I should be looking out for? Like, do I have to set up a particular kind of database, or use a particular kind of template? Is inserting the client's name mid-sentence a particularly difficult thing to do?
Any help to get started on this is much appreciated. THANKS.
When I use the filter by form, filter by selection, and toggle filter buttons located on the ribbon, they work normally on my form, in the usual way.
However when I use the button wizard to add the various available filter buttons to my form, it adds the buttons with the correct graphics on - but in form view clicking on the buttons does not actually do anything.
I've tried running compact and repair but still the same.
I have a form that uses a listbox. I can select multiple items and then update a subform. When I press a button with VBA and use the filter property on the subform for the first time, nothing happens. When I press the button a second time, it does - and it works thereafter with different selections. It only happens that first time, after loading the form and pressing the button for the first time.
I have tested that the list items are accessible via the vba variables and stepping through my code and the problem starts with the filter properties.
is not switched on first time (I have tried a requery and refresh statement after this). When I step through the code the first time, the FilterOn property remains false. A second button press and it is fine.
I have a continuous form (FormA) which shows records from TableA. There is a button on FormA which opens FormB. FormB allows a record to be added to TableA. On closing FormB, FormA should requery to show the most recent addition to TableA. This does not happen though, it is always a record behind, unless I close and reopen the form.
I'm using;
Forms![FormA].Requery
on my close button on FormB.
Any ideas? I have done a search but cant find a solution.
I am brand new to MS Access 2013 & VBA.There is a form that collects the data and writes it to a SQL database. The form is a certificate order form for a school. There are two tables that data is written to. dbo.CertificateMaster & dbo.CertificationModules. There is a relationship between the two tables. A person will request the certificate and included in the request are the subject(modules) that the person has studied.As part of the request the college/school needs a printed copy of the request.As the report opens I need it populate certain fields with their full names. To do this I use the following script
Private Sub Report_Load() Dim strSQL1 As String, db As DAO.Database, rs As DAO.Recordset Dim strSQL2 As String, db2 As DAO.Database, rs2 As DAO.Recordset Dim strSQL3 As String, db3 As DAO.Database, rs3 As DAO.Recordset
[code]....
Problem 1:
When drawing the data from dbo_tblCourse_Department I need to populate 4 textboxes on the report. I am able to populate PgmTitle &NQFLevel, but I keep getting "Item not found in this collection error for Credits & SAQAID.
Problem 2:
I need to populate the various courses with their name which is extracted from dbo_tblCourses. However only the first course name is visible in the report. I have tried all sorts of different iterations with IF Then or Do While to get the names in the correct line of the report.
I have a pop-up modal form which is loaded (like 99% of my other forms) by a button on another form.
Unlike all of my other forms, however, it doesn't load its background properly. If I drag the window off screen and back or shrink and resize, it does look right, so I guess maybe it requires a refresh?Obviously, I don't want to rebuild the form from scratch, so maybe some vba which refreshes the screen?
