I Need Help With Properly Re-creating And Old Form. (I'm New To Access And Have Q's)
May 29, 2006
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.
I have built a rather extensive database using Access 2000. When I install it on an Access 2003-enabled machine, the first time it is opened, the user is presented with the following errorbox:
"An error occurred and this feature is no longer functioning properly. Would you like to repair this feature now?"
At this point, clicking "Yes" results in an update the installation of Access 2003. Then the database must be opened manually again. Then, the following is displayed:
"Module not found."
Closing the database at this point and then opening it again results in a normally functioning database.
I have several questions:
1) What is the feature that is no longer functioning properly? 2) What is updated when the installation exec runs? 3) What module is not found? I assume this is one of the VBA modules that I've created, but I've check all 62 of them and they all seem to be there available. 4) My first guess was a reference problem, but I've checked the references used and all are available, as far as I can tell. These are the ones that I have referenced: VBE6.DLL, MSACC9.OLB, DAO360.DLL, STDOLE2.TLB, MSCAL.OCX, MSADO25.TLB, MSO9.DLL. 5) After getting the first error - the one about nonfunctioning feature, I clicked "no" to the repair now question. This does nothing except the database remains open. At this point, I opened the code and tried to compile, but I get the "module not found error". I likewise tried to set a breakpoint in one of the first modules and I get the same result. 6) I even tried to copy the forms, queries, tables, and reports into a blank database with no references and no other extraneous code and received the same result.
This site has been extremely helpful in the past and I am really relying on you guys again for the same stupendous help now.
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'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?
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 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?
I have a Main Form with a tab control. On each of the six tabs is a SubForm that takes up the entire tab. So basically I have a tabbed interface for my forms, just with a bit more control over the layout than if I just opened the forms separately in the Access window.
Anyway, on one of those subforms is a button. When you click it, it opens a separate form in a pop-up window, which is used to find a specific record. Once that record is found, you can click another button, and the idea is that this pop-up window closes, and you return to the underlying form, which jumps to the record you selected.
So far so good. Now, if I am working on my program in Access, and I open that subform's source form separately in its own tab, click the icon to open the record finding form, find a record, and then click the button to load that record, the following code runs, and it runs flawlessly - closing the pop-up window, and passing the information back to the main form which displays the proper record:
Quote:
Private Sub ViewRecord_Click() Dim RecordID As Integer RecordID = Me.ID DoCmd.OpenForm "CDLExam", , , "ID = " & RecordID DoCmd.Close acForm, "CDLExamCONT", acSaveNo End Sub
Where my program falls apart is that, in actual use, this form is not open on its own. It's open as a subform on one tab of a tab control on a Main Form. So the third line of code falls apart. Access thinks I want to open CDLExam separately, but it can't because it's already open in the subform, so instead I just end up back at the main window like I want, but the form fails to move to the proper record. Basically, line 3 just doesn't do anything.
How can I make this work? I tried replacing "CDLExam" with the name of the main window, but then it tries to move to the record in the main window, which throws an error as the main window doesn't even have a record source attached to it.
how to refer to the SUBFORM which has CDLExam open, and tell THAT to move to the proper record.
In one of my databases, I have a form and a report where the images stored in my tables are not showing properly for all entries. They show as an icon with the file name rather than the image itself. This is not true for every entry, so I don't think it would be a problem with how the form and report are set-up.
I have put much time in designing a form. After exiting and saving it, I cannot now open it. Only the Page Header shows, and I cannot get into 'Design' mode. I have tried repairing and compacting to no avail. Any ideas?
We use our CRM for entering client orders, which is done through our 'OrderEntry' form. On that form we have a subform that we use to enter order lines - as we have a range of products on offer, an order can be made up of one product or anything up to 100.We have an 'amount' textbox on the subform, that the users enter the cost of each line, and as they add more lines the total cost of the order is calculated using a textbox in the subform Footer, with the ControlSource set to =Sum([amount]).
On the main form, we have a Net textbox, that the user will add in the total cost of the order once they've added all the order lines, and providing the =Sum([amount]) textbox on the subform matches the figure in the Net textbox, a button to Proceed the order and go on to the next step appears.
The intermittent problem I have, is that the OnCurrent event I use to show the Proceed button does not seem to fire on its own - but when I stop the code and step through it, it will show the Proceed button without any problems.Here's the code:
(the Proceed button is referred to a 'Command80')
Code: Dim OS As String Dim UT As String OS = OrderStatus UT = fGetUserType
[code]....
I've made the part that refers to the Proceed button bold, but thought I'd add in the whole OnCurrent event in case there was anything in there that was blocking it.
I added in the MsgBox code at the bottom to make sure the OnCurrent was firing, and that works fine.I've searched through the rest of the code, and there's nothing else in there that references the Visible property of the button.I've been through the decompile process detailed here, and also been through this similar thread with a fine toothcomb and this still won't work.
I've saved the form out as a textfile and then imported it back in, both through the immediate window, and short of importing everything in to a new DB and starting again
I have a form (frmAddManifest) with a subform (subfrmManifestTransporters).
When creating a new record, I can enter data into frmAddManifest, but the subform doesn't update to link with the record - I presume it's because the record from the main form hasn't been completed yet.
Is there a way to requery the form and/or subform so it stays on the record I was working on, and link the subform properly?
I'm creating a tabbed form in Access 2007, and in the first 3 tabs, when I dragged the field controls onto the form, they stretched to the width of the form and all stacked nicely underneath each other.
Now on the next tab they are coming out as much smaller. I don't want to stretch them to fit as I want them a uniform size and I just want to find how to get that setting back! I've been messing with the anchoring buttons to what seems like no avail!
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".
Need help and advice desperately, I am trying to update about 10 tables each time a visitor visits my site.. however there are often missing counts, meaning the total data in each table are not the same frequently They are supposed to be since all the tables get updated each time. Im using MS Access 2003. What could be the problem?
Below is an example of one of the function that updates one of the tables in my DB. ************************************************** ***** function GetIdRes(sName) 'Get ResID sUserID = Request("UserID") sSQL = "SELECT ResID, ResName, Total, UserID FROM Resolutions WHERE UserID = '"&sUserID&"' AND ResName = '" & sName & "'" rs.Open sSQL,,,adCmdTable if rs.eof then rs.AddNew rs("ResName") = sName rs("Total") = 0 rs("UserID") = sUserID end if rs("Total") = rs("Total") + 1 rs.update GetIdRes = rs("ResID") rs.close end function ************************************************** ****
I've got another 10 similar tables. The Column "Total" is often different among the tables.
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.
in the criteria box, but every time the query runs i get a parameter box "forms!training courses!course_dates subform control!course_dates subform!course date" asking for the info.
Seriously I am totally stuck on this and i think all i need is one perfect example... please help!!!
I had a look on Microsoft Support and found this article: http://support.microsoft.com/kb/242889/en-us but I don't understand what it's saying.
Basically, I am trying to get the message formatted as it suggests in the help file (Access 2002) i.e. first line bold, second line normal, third line normal and paragraph space, by using the @ symbol. However, it is not recognising this as a format command, just plain text.
MsgBox shows: Please enter a date range.@Hint: to show all records use a date like 1/1/1990.
instead of:
Please enter a date range. Hint: to show all records use a date like 1/1/1990.
The MS Support file above seems to suggest that this is simply no longer available in later versions of Access, and offers two work-arounds. However, I have no clue about the second one because I'm not really into programming, and the first one seems to suggest exactly what I am currently doing.
Can anyone help me step through what I am doing wrong? Or is it just a case that I would have to use the code? (In which case could someone help me set it up?)
I created an MDE file for my application it's a split db when I hit the exit command button on the MDB version it quits the application correctly but when I do the same thing on the MDE version, it leaves the database window open on the taskbar.
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'm using access 2003 sp2 with all the current updates. I've got several tables linked to a sql server 2000 DB. We use it for an app called Altiris. Most of the queries i've written from these various tables work fine. However, a couple of them for no apparent reason, do not display the correct data in the data sheet view when the query is ran.
for example, I have a simple query with two tables linked. The first field is asking for the "name" column of one of the tables. In the criteria of that column, I put something in to limit the names of the programs i'm looking for. Lets say I use LIKE '%adobe acrobat 7.0%' When I run the query, it returns a certain number of records, but in the name column, it has all wrong info. I just ran it and it came up with 4 records: "webcast....", "abacast client", "Ibm websphere", and something else. Nothing even close to "adobe...."
So then I export the query to an excel spreadsheet and then open. Guess what? It has the correct info. Four records of adobe acrobat. The other column I have is from the other table and is just listing pc names. They displayed properly in both datasheet and excel file.
does anyone have any ideas what could cause this? I've deleted the table and linked to it several times. I've used this table for many other queries and it works most of the time. I just have no idea what is causing it to do this for only a few queries.
First some background. A few months ago I replicated an Access 2003 database so that folks in various locations could all enter data. There is one master and 3 copies. One of these copies was operating on a computer with Access 2000 and it also happened to be the copy of the database that gave me some headaches (never with the synching) which I am almost positive were related to the version difference.
So, despite those other problems this copy of the database synched up fine throughout the summer. Now all the sudden I have found a few recently entered records in the copy which are not being transferred during synchronization. And from what I have been able to tell so far they seem to be from a particular data entry session on a particular date. I have already looked through the records in question to see if they are missing an important field of data or something and they seem to be complete and legitimate records.
I have tried a number of things and at this point am at a loss as to where to go next. Does anybody have experience with replicated databases and potentially run into this problem before? Any advice on what to look at next to figure out the casue and the fix?
