I have just noticed some strange behaviour in access. I hava and asp page which gets two dates which are then used in a sql statement to refresh a page. The end of the statement deals with a between clause like this
WHERE tbl_Movements.date_of_Movement Between #12/03/2007# AND #30/03/2007#
But when I view this in design mode it changes it to
Between #03/12/2007# And #30/03/2007#
Why does it do this and what can I do to resolve it
After working great for 12 months my db has suddenly stopped working tonight! after opening on to the first form and clicking off any field I get "Invalid Data Format" error message pop up and transfered to the code windows. Access then tells me it does not recognise the code and shuts down !!! Has anyone got any ideas PLEASE, this is a disaster
I have a table called 'students' with the appropriate fields. There is another table named parents_prof which contains records of common professions. The tables are related through the 'ID_parents_prof' of the 'parents_prof' table to two fields on the table 'students', thus 'students.father_prof' and 'students.mother_prof'.
1st question) Is that possible? Is it possible to create a relation between the key field of one table to two different fields of another table? Is it a good practice?
I did this because I want to build a query which checks for both fields together, meaning I want Access to return a record if the appropriate profession is in either field of mother's or father's profession.
FROM parents_prof INNER JOIN students ON (parents_prof.ID_parents_prof = students.mother_prof) OR (parents_prof.ID_parents_prof = students.father_prof)
GROUP BY students.surname, students.name, students.father_prof, students.mother_prof, parents_prof.parents_prof
HAVING (((parents_prof.parents_prof) Like "*" & [enter profession] & "*"));
Access cannot represent the above graphical, so I had to enter the
OR (parents_prof.ID_parents_prof = students.father_prof)
in the SQL formula.
2nd question) Is the above proper query, according to the rules?
Hello, Something weird happened to me - I have a form with continuous subforms and on it are checkboxes. When I click on one specific checkbox, a text box on the parent form becomes visible, where I can add text, which is added directly to the table in the AfterUpdate event. However, the query underlying the subform does not reflect the change, and when I either go to a different record on the parent form, or click on another checkbox in the subform, I get an error message:"This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. [...]"I can then Save The Record, Copy To Clipboard or Drop Changes.When I drop changes, the text shows up in the query, although my textbox disappears.The one time I selected Save, however, the table got messed up and a foreign key was replaced by what looks like random number [used to be "1", is now "4587525"]. A second foreign key was also changed.Can anybody explain this?And, more importantly, suggest a way to avoid this entire situation? Thank you.
Hi, i have a form which is acting strange. i press a button that runs a macro and opens a second form based on my PersonID. ( opens the form showing that person only ). it works great for the first 2 table records. after this, i get a blank screen. not even any text boxes. i have a "where" condition which reads on the following macro sequence:-
OpenForm ( MakeYourPayment ) View ( Form ) Filter Name ( ) Where condition (="[PersonID]="PersonID] ) Data Mode ( Edit ) Window ( Normal )
my second form has PersonID field available so as i read it, my where condition should open the form using Person ID from min form to PersonID on the 2nd form.
any help greatly appreciated as there is a lot of work on this form.
slightly related to a previous thread of mine. thanks to boblarson and co, i have created a subform on my main form that is blank. then i press a button and the sourceobject is set to the desired form i want to have visible. i have the following code-
Private sub Button_Click()
me![MainFormSubForm].sourceObject = " Edit Person" ' name of form me![MainFormSubForm].LinkMasterFields = "PersonID" 'field on main form to link from me![MainFormSubForm].LinkChildFlields = "PersonID" ' field on subform to link to
i can get 4 out of 5 forms to link correctly. i have a listbox on my main form that changes the mainform data to show a different person.with the other forms when they are visible, i can click a different person in the listbox and all info changes. form number 5 "Edit Person" doesnt work. when the form is loaded via the above method, it shows 1 record which is the first in the query that the main form is sourced to. when i click another person in the listbox, the subform goes to a (new) record and does not select the person clicked. the main form changes but not the subform.
why does it do this on one form when 4 others work with the code? the same code is method is used.
This outputs smth SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('Dot') ------ But not this...why ??? I am using ms access SELECT Proj_Name & '-' & Proj_ID as Emp_Pr from Emp_Prj where trim(Proj_Name) like trim('%Dot%')
i have an Address textbox that has key behaviour as - new line in field. this allows me to put the complete address in one box, post code is another textbox. this is fine if i want to create window envelopes but - if i want the address to look as one long line in a report it wont work since the address in the form takes up two lines -- only the first line shows in the address column of the table - hence only one row shows in the report that derives from that table SO =[Address]&" "&[PostCode] placed in the Source Code of the address only shows the first line plus post code . if i make the box deeper then it will show - but only as a second line - i want it all to be one line can i get out of this and still keep the -new line in field- behaviour? thanks in anticipation
This is for information only - I am not seeking a solution.
I created a Select Query selecting only two memo fields from a single table.
The query works correctly unless you select Unique Values = Yes which results in the error message "The field is too small to accept the amount of data you attempted to add. Try inserting less data."
Hi. I have a question prompted by database behaviour that I do not understand.
I have two tables with related records, one being a subset of the other. I have joined the tables in a query with a Left outer join relationship. I have put the query results in a form, and when a User clicks a Yes/No box, a new record is created in the subset table automatically generating an ID number using an Autonumber set to start at chosen integers. However, recently error has occured. When I view the query results some records will not allow me to create a record in the subset table stating "You can not add or change a record because a related record is required in table x", eventhough that very record is the record which the query is displaying. Some records will. The behaviour is erratic and I cannot see what is causing it. Might this be indexing related?
I am using a form with 3 combo boxes name, weight and grade. The combo boxes are cascading:
A name is selected in the first combo box, since each name has different weights attached to it only the weights associated with that name are selected when the weight combo box is clicked. In a similar vein, there are only certian grades associated with a certain weight.
The effect is to filter the results so that an invalid entry cannot occur.
My problem is that once an entry is made and I select a new name for the next entry, the weight and grade boxes remain with the same values instead of updating to new values. Updating of the boxes does not appear to be working.
Does anyone have any ideas how to solve this problem?
Why does my sub form which is contained in a Tab, not display unless a certain field is populated with something? It's not a required field. Infact it is one of two identical fields... a relationship and a lookup to another table. It doesn't matter if the second one if filled in or not. Just this one. The record is in the database, I choose from a drop down in the header... and the subform then requeries and displays the information. However, unless this 1 field is filled in.. the subform remains blank. It is getting on my nerves!!
A team id can appear in either team1 or team2 (depends on how the algorithm to compute who plays who calculates it).
I want to know how many games a team plays, so I wrote 4 queries:
qryTeam1Games: SELECT tbl_Fixtures.team1_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games FROM tbl_Fixtures GROUP BY tbl_Fixtures.team1_id;
qryTeams2Games: SELECT tbl_Fixtures.team2_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games FROM tbl_Fixtures GROUP BY tbl_Fixtures.team1_id;
qryAllGames: SELECT * FROM qryTeam1Games UNION SELECT * FROM qryTeam2games;
qryNumGamesByTeam: SELECT qryAllGames.team_id, Sum(qryAllGames.games) AS SumOfgames FROM qryAllGames GROUP BY qryAllGames.team_id;
Unfortunately, if the same team_id and games combination appears in both qryTeams1Games and qryTeams2Games, it doesn't bring 2 records into qryAllGames and the summed number of games is only half of what it should be.
Is this a case where a union query shouldn't be used? Is that how it is supposed to work?
A work around isn't a problem but this did have me stumped for a bit.
I am hoping someone might be so kind as to help me with a search form I am working on. I have included a test database that has the form. The form has a search box that works dynamically to show results based upon a string search as typed into the search box. The problem is: if I type any spaces beyond the last digit of what I am searching for it no longer displays the intended result.
For example; there is a field called - serial - in the test databse you will see a value in that field of 319-04. If I type 319-04 it shows that record only. However if I key in any additional spaces after the 319-04 it will not show the result.
This is just a problem because I am trying to input the data using a scanner. I have found that if I scan the barcode with the serial# it seems to include two spaces after the scan. I don't know why that is...but rather than worry about that I would just like to try and get the form to display the record even if there are spaces after the last digit entered.
You start to type with a smile on your face Form_MyForm. <-- from that point (literaly) you usualy have a list of things you can do with that form.
All i wanted to do is a requery but i have no list...and even if i type myself Form_MyForm.requery .. this will not work cause it says that it cant find the dang form.
I have tried renaming the form, let someone else type the name for me, copy&paste ..nothing work, when i get to the point...it never gives me a list of things i can do with that form.
I have done some forms and it is the first time that i had this problem: I can input data in several records without having any problem but when i close and save the form and next time i open it ,i do not find my previous records. I checked this in the tables and find that the values that i had input were there but in my form these values had disappeared. Can anyone explai this ..
Also,
Suppose i have 2 tables with NAME set as the primary key and which is linked 1-1. When i create a form for these 2 tables and when i write JO in the name field only once the name JO should automatically be inserted in the other name field i.e i should be able to input only one data. This is not the case. What i should do is : write JO then click in the other NAME field then type something e.g RO then this will be typed auto. in the other Field. Can you also explain this
I have a form with a tab control and each tab has a subform on it. 3 of these tabs/sub forms are continuous forms that allow the user to enter details for the selected job (i.e. on tab has a subform that allows the user to enter various scheduling dates for the job).
If I say have the scheduling tab selected and then change a field in the MAIN form, then click on the new record row in my continuous subform my main form jumps to the first record. However, if I update a field in the main form, select a different tab, then click on the new record row in my subform it works fine. Also, if I click the add record button it works fine as well.
I can't seem to find anything in my code that would cause the jump. Any ideas?
I was going to create a new query with the simple query wizard, but as soon as selected a data source I got a message saying "The expression on Got Focus you entered as the event property setting produced the following error: The text you entered isn't an item in the list."
After this I can select a datasource, but the cursor remains in hourglass mode. It almost seems like this is a run-time error, when I am merely trying to create a new query. In addition to which, I haven't set any events to run on "got focus". Anyone have any ideas on what is causing this and how it can be fixed?
I have a strange problem which have to be solved in Access in all way, if it is possible. :-) I have to create a table similar to an Excell sheet. Row headings are name of persons, column headings are workplaces. The aim of this table is to register worked hours at each workplaces per persons. At one person in one shift more workplaces are possible. The number of persons and workplaces is changeable. Does anyone know how could I solve that to store in different records each workhours? For example if a person works 4 hours at 1. workplace and then 4 hours at 2. workplace, this would mean only one row on the form, but two records in the table. I would be very grateful if somebody would have a good idea! Many thanks in advance!
I was working on the design of a form and had just made, what I thought, were some fairly minor changes. The specifics of which I can't recall. On saving the changes and attempting to go back to the form view I got a pop up window with the following header;
Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.
I can either send and error report or choose not to :rolleyes:
The up shot of all this is everything else in the DB seems to still work fine, but I can not open the effected form in either design or form view without Access shutting down :mad:
Has anyone seen anything similar? and if so any idea what may have cause this dummy spit? and Is there any way around this without deleting the effected form and rewriting it?
Hi to all, Here is my problem: I import value from an excel sheet and write them in a table, i use Excel object reading cells row by row. All works fine but after 1900 or 2000 rows program hangs without errors if i place a stop in program when my row counter reachs 1900 and run the program step by step that works, i tried to place a sleep command each 500 rows without better result. Does somebody have any idea ? (i use Office 2000). I can't use transferspreadsheet because for any reason in my excel table generated i have #Number! errors in some fields and i don't know how to skip these records. Thanks in advance for help. VINCENT
Has anyone seen the message in the attached file? I got this when I was trying to update the row source on the field after I had a problem with data in a report coming up inaccurate. I've been developing in MS Access for about 7 years now and have never seen this. Any explination for this would be greatly appreciated.
I am using MS Access 2000 on a Windows 98SE machine. :eek:
I have a query that in some cases it works and in others no. I've tried :confused: everything :confused:. The problem is very strange: attached there is the database! Anybody have any idea?
Thanks!
P.S.: :) forgive me but I don't write well the English!
It may be a simple problem I don't know. I have to do an inventory control database in Access for my Systems Design paper however I am having an issue with queries.
I have a query called Item Status Check and it asks the user for the item code and goes in a table called Inventory Item to get the results. Now the problem is after I added a whole lot of new items (I had some from testing earlier) it does not bring them up in the query at all, its ignoring them basically.
Now I find this strange because other queries like suppliers etc still work fine and access any new data I put in.
I know its going to be simple but its doing my head in.
Now if I delete the stockonhand it shows all the stock so thats the problem. The way I have calculated stock on hand is to simply take all the purchase orders, add them together and subtract the customer/production order numbers. I don't know why this would happen
EDIT 2
found the problem, need the solution
In the StockOnHand calculation is takes all the number of incoming stock and subtracts the outgoing stock to get its answers. If the stock isn't on one of each of the incoming/outgoing forms it doesn't show it at all.
EG
Incoming Stock:
Purchase Orders Add Stock (from an alter stock form for discrepencies)
Outgoing Stock:
Customer and Production Orders Remove Stock (alter stock form)
If items have not been used on 1 of each of these they don't show up in queries with StockOnHand.
Hello all. Have a bit of a strange one here (and fairly frustarting). I have a table which includes a field (type memo) which holds text comments.
I have a query which includes this field. For some reason, when the comments field is empty (identified thorugh opening the table), when it is returned through the query is shows two characters, namely '@v'.
Can anyone shed any light on this for me? Thanks for reading :)
which contains the expresion forms!frm_itemslct!text8 It should just pick up the value contained within [Text8] which is a number, and it was doing that earlier this evening. I've since changed something and instead of a number it is now showing a small square :confused: For the life of me I can't work out what it is that is causing this.
