I'm trying to create a simlpe database that is use to store time worked for clients (see attached).
If you run the form frmClientEntry i have two issues.
1 - you will see that the title is automatically selected from the combo box. However is i try and enter the form I'm told 'You cannot add or change a record because a related record is required in table 'tblTitle'. If I select one of the entries I get rid of this. I would like the default to be selected...
2 - There is no entry in the hourly rate column field even though there is an entry in the table. How do I get my hourly rate to show? Ideally this will only be a single text box and not a comboBox as the hourly rate will be the same for every client.
If this is a table design issue please let me know and I'll post there.
I have a query based continuous form that looks up a serial number entered by the user and allows them to view the matching data. When a user enters a serial number that is not in the database I want a message box to pop up to warn them there is no match.
The code I am using makes the msgbox pop up on every entry. I have tried to put this code in a variety of form and control events with no luck. Can someone tell me what I am doing wrong?
Private Sub Form_AfterUpdate() Dim intRespone As Integer
If DCount("[packing station scan].[serial_number]", "match with last test results", _ "[drive test results].[serial_number] = ' & Forms![match with last test results].[Serial_Number] & '") < 1 _ Then intresponse = MsgBox("Stop! Serial Number has no test data!", vbYesNo, "No data found") Select Case intresponse
hi... firstly, i created some report based on query. then in my form i create list and combox to list down all primary key that had in query.. what i want to do is open report, based on criteria that i selected from combobox or list box... i create both cbo and list box to compare which 1 is better... but both not working. i mean it works but not show the data as i want to. it open / list all data at report..
I have a field in my DB called "Category". Users are allowed to imput any text in that field. I have a particular problem in which the LIKE command isn't working as I think it should with the limited help files of Access explanation of use. The category field has multiple entries, for example - HEQ, PBH5, PBH4, SWA, SWA2, ALL - etc. I'm trying to pull the records from that field with specifics.... in other words I may need to get All the records that have "PBH5" in them. However, if if PBH5 isn't the FIRST entry in that field the LIKE criteria doesn't work. I have tried Like "PBH5*" LIke "PBH4* etc to no avail. Any ideas? It can't be that difficult, but I cannot find anything in any book I've purchased on it, or any of the knowledge base information at Microsoft.com.
I am working on developing a database that develops requests to send to another department. There are a lot of forms, macros, reports and queries. A user inputs information into a form, and then selects a command button that is attached to a macro that builds the request. Midway through the build, another form comes up to complete missing information. I was going along just fine - at the testing stage for this part of the process - when the database stopped working like it should. Some of the issues I am seeing:
1) If you open a new query and try to close it without saving it, it just keeps asking you if you want to save over and over even though you select No.
2) If you open an existing query and make a change, it closes it and saves the changes without any prompting at all.
3) Macros and forms do not work right. I have a macro that deletes the records from one table. The form for that table now shows #Deleted in all the fields instead of blanks.
If you compact/repair the database is good for one run through but then it doesn't work correctly again. I have had this issue in the past but have no clue how I break my databases! I have a general knowledge of Access, I am not really a VBA coder if I can avoid it at all costs. There must be something in the relationship between all my tables, queries, forms, etc but I have no clue even where to start to troubleshoot. HELP!!
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id FROM ft_payees AS p, ft_txn_summary AS t WHERE p.payee_id=t.payee_id And p.market=t.market GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "") WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
Hello all. Hope I describe my problem correctly. :D
I have a near complete database with forms linking to tables. Tables have relationships with keys set to ref. integrity/cascade records.
Here is the problem -
My forms are meant for people to input data into these tables. However, I do not want them to change those keys.
Problem 1) If I set primary/foreign keys to "autonumber" I cannot get the relationships to recognize ref. integrity and the records will not cascade, and will not update.
Problem 2) If I set primary/foreign keys to "double integer" then if someone enters a new record into the forms, it is recorded as "zero" when it should be updating in numerical order (I have 479 records in the database, so the next new record should be 480).
Problem 3) If I change it any other way - from double integer or auto number, the tables do not update at all, or do not update correctly.
What is going on here? I thought creating forms was a fairly simple task - create the form, it is related to the table, update the form, it updates the table. I mean, duh. :confused:
Thanks anyone for your help, because Microsoft's site sux.
Made some tables with data, then wanted to filter it by form with comboboxes approx 10 of them, so started to write a simple query but it doesn't really work. Then I found this forum and when started to review some threads, noticed that some (most) of you write a query in vba under a button with on_click function.
The question is, what is the correct way of writing a query? Is is a vba code under a button, or is it a code in the actual query?
I have a continuous form that is using a select query to populate all of the bound controls. The drop downs and text boxes are displaying the values from the correct places. But, the form is not updating in the places I expected.
The select query that is being used to populate, combines information from 3 tables.'TimeEntries' table (the one that I want records to be updated in). This holds employee ID, and numeric values for Project Name and Project Task.
'ProjectInfo' table (nothing should be updated here). This table holds other details about the project (ex. Project Name) that is used to populate a combo box. 'ProjectTasks' table (nothing should be updated here either). This table holds details about project task (ex. Task Description) that is used to populate a combo box.
The issue I am having is that when records are updated on the form, they are adding records to all 3 tables that are a part of the query, instead of just updating the time entries table.
Record Source (form) = Select Query "EmployeeTimeEntry"
I have been having issues with a subform on the attached database - for some reason it is not always updating to show corresponding records.
What I am planning, this is effectively going to be an interactive learning portal which can test users as well. There will be learning material (not yet included) and a bank of questions with corresponding multiple-choice answers, only one of which is correct. Each time the main form is loaded (Cat1MainForm) it randomly selects and orders questions, then, via a sub-form, returns three potential answers. There will then be radio buttons with which the users can answer appropriately.
If you load up Cat1MainForm and scroll through various questions you will see that sometimes the answers appear, other times not. Sometimes on one record the answers are there, you navigate away and back, and they have disappeared, and vice-versa.
I am using Access 2007 with Windows XP. I have a scatter chart embedded in a form that is plotting data from a subform with a trendline that is extracted and used on the form for calculations. After having the table set up correctly and running fine, I closed the database and then reopened it to have the table plotting the data in a made up 1 to 1 relationship rather than what is in the datasheet. The only way I have been able to fix this error is to change the chart type to something else then back to a scatter plot and remake the trendline. Why this is happening and how to prevent it?
I have a form based on tblContact with a subform based on table tblCategories, and another subform based on tblCompany. I have a one-to-many relationship between tblCategories and tblCompany, with the pk in tblCategory linking with a fk in tblCompany. All of the fields in tblCategories/ sbfrm Categories are yes/no fields, not sure if that plays into my problem at all.
When a user checks one or more of the check boxes in the categories subform, closes the form and re-opens it, none of the check boxes are checked for that person. I know this has something to do with the relationships and how the categories are linked to the contact... but I can't get it straight in my mind as to how that should be set up.
I have a form "FrmMaintenance" based on the query "QryMaintenance" that is taken information from the table "TblMaintenance".
In the form, I have to pull the serial number of a machine from a drop down list which works perfectly fine in the form and it also updates other associated records.
When I go to the table, it puts associated number instead of the serial number that I've pulled.
Same thing happening for the pull down list "Engineer".
I've tried to look at the format of the "SerialNo" and changed to text, then to Combo Box but it didn't work, and the same for "Engineer"
I have created a piece of code that filters a sub form of diary records using criteria the user has selected or entered. It was working fine when I made it a month or so ago and now is seems to be returning incorrectly filtered records, for example, I enter 2 dates to return all diary entries between the 2 dates. Yet it misses some records out that should be within the date range, and it sometimes include records that are outside the date range. I am also getting an error when I try and filter the diary entries via the supplier, "data type mismatch", here's the code that, bearing in mind, was previously working fine.
Code: Public Function filter_diary() Dim dbs As Database 'Dim qdf As QueryDef Dim Sqlstr As String Dim sqlstrwhat As String
when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.
Code: oListBox.Selected(lngRow) = True
However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).
Code: Print oListBox.Selected(lngRow) ->True
Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.
Code: oListBox.Requery
This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.
i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.
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 recently upgraded to Access 2007 from 2003. The upgrade was in June and went smoothly. I added a couple of drop down boxes to my form in August and that seemed to have gone fine as well. Suddenly, at least two weeks later, my form buttons are not working. I added the pathway to my database to the list of trusted locations. Everything I have read says this should work. This did not make a difference. I am able to edit forms, run queries and reports, and can add new records by entering data directly into tables but am not able to add new records, view details, close forms, or exit using my buttons/forms.
I use the following module to run an audit trail on my database, which is then linked on to the form by using the following code on the BeforeUpdate property of the form. This works perfectly on the form itself - and in tabs - until I am using a subform. How can I get the module to work without having to name each subform (this form uses 3 SFs!)?
Code: Sub PropertyAuditChanges(IDField As String) On Error GoTo PropertyAuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As Control Dim datTimeCheck As Date Dim strUserID As String Set cnn = CurrentProject.Connection
Hey guys, i have an access project for my computer class, and considering i dont know much about access, i think i did ok, but i want to make sure everything is the way it is suppose to and do it myself than paying someone else to do it for me.(its some pretty interesting stuff actually) so can you gurus, let me know if what the email from my teacher said is what i have done? i think its just the relationships that might not be done right if anything. i attached the pdf file that says what i must do, and the access file that i made. thanks for all your help in advance guys, Quote: Originally Posted by Kush's Prof there is nothing to make the relationships work. you are simply confusing the operational aspect of the database adn the design aspect of it.
When you program an interface to use it then you worry about usade of the relationships. Setting them up at the design level where you are now, will simply ensure that the relationship is not violated when it is programmed. That is it.
In my database forms, i try to use Ctrl + F and its working fine. But when i link that form to a Navigation form and tried ctrl + F , i got the following error message, how to fix it.
When i am clicking on refresh button the query which is generated by drop downs is not updating in the form, but it was updating in the query table. How can i refresh the query on the form where drop downs are..my code is
forms![Opening]![RepQuery].Requery
where Opening is my form name, repquery is the query which i generated in form.
I have a form ("Contacts Form") that I use with a few subforms on it. One of the subforms is "sbfrmCompanys" that has information on the contacts company. One of the fields in sbfrmCompanys is "Organization." Users used to be able to use the "find" feature to search for an organization's record, however the "find" feature suddenly stopped working. Now, when a user searches for an organization that I KNOW is in the company table, a dialogue box appears that says Access has finished searching and no records were retrieved. This cannot be so because I know for a fact the particular value I am searching for is in the table.
I have a form where the control source of several text box fields includes a function that looks up values in a table that is not the form's record source. The default value in the property for these fields is set to 0. Unfortunately, when I migrate to a new record, these fields display the dreaded #Error. It should be noted that these fields are for display only and are not even in the form's underlying table.Here is an example of the control source for one of the fields:
Public Function GetFYSummary(strField As String, lngProjectID As Long, lngFYYear As Long, intPeriod As Integer, _ strWBSType As String, strSource As String) As Long
[code]...
I'm trying to cover my bases to ensure that the displayed value of the field shows 0 rather than #Error.I even tried including a breakpoint at the first line of code in the function and it is never even triggered when I move to a new record.As soon as I type the first letter/number in a field that is tied to the form's record source, all of these display values do change to 0 appropriately.
I have 2 combo boxes, one Categories and Products. However I have follows a tutorial and when I select the Categories, the products do not show even though the tutorial says it does. I have attached a copy of the DB.
With a many to many relationship using a junction table, do I have to manually plug in the ID numbers from the related tables in the junction table for it to recognize the related data? It sounds silly and obvious I guess that you would have to, but when I go to create a form based on a many to many relationship, I would like the ability to enter data without having to enter the related ID numbers in multiple places on the form or subforms to make it work. I guess I envisioned access using auto numbers to automatically update the junction when I wanted to add data related between the "main" table and the distant table joined with a junction. Can this be fixed using a Parent/Child relationship set-up in some fashion?
I am trying to get a blank ribbon to be in place when a user opens my application. I have an Adp linked to SQL Server. If I create a table in SQL Server named ribbons and have the app read the data in that table in the AutoExec macro (this runs the code that reads the data) then assign the new ribbon to Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value it works and the blank ribbon is displayed. The problem is that this is no use because if the adp is not connected to the database on startup it causes an error as no connection has been established.
Instead I have put exactly the same xml file into a module but this does not have the same affect even though I call the same Application.LoadCustomUI method. I havent really done anything different except change how the xml is found. The problem with this method is it just pops the new ribbon into the list of available ribbons so then the user would have to choose the ribbon, shut down and restart, that is no use.
Has anybody got any idea what can be done about this? I will post the code below.
Thank you for any advice on what can be done.
Paul
First method (which works but is no good for distributing the app)
Autoexec calls the following Public Function LoadRibbons() Dim RS As Recordset Set RS = CurrentProject.Connection.Execute("SELECT Ribbons.RibbonName, Ribbons.RibbonXML FROM RIBBONS") If Not RS.BOF Then Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value End If RS.Close Set RS = Nothing End Function
but using the following and calling it in autoexec just puts the ribbon in the list and doesnt use it
Function CreateRibbon() Dim xml As String xml = _ "<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & vbCrLf & _ " <ribbon startFromScratch=""true"">" & vbCrLf & _ " <officeMenu>" & vbCrLf & _ " <button idMso=""FileCompactAndRepairDatabase"" visible=""false""/>" & vbCrLf & _ " <button idMso=""FileOpenDatabase"" visible=""false""/>" & vbCrLf & _ " <button idMso=""FileNewDatabase"" visible=""false""/>" & vbCrLf & _ " <splitButton idMso=""FileSaveAsMenuAccess"" visible=""false""/>" & vbCrLf & _ " </officeMenu>" & vbCrLf & _ " </ribbon>" & vbCrLf & _ "</customUI>" Application.LoadCustomUI "BlankRibbon", xml End Function
