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 :)
I know its old in the tooth but this project has to be done in Access '97 with some unusual results:
the following code snippet:
Open strFileName1 For Binary As 1
strCDR = rsName("SOurce CLI") & "," & Format(rsName("date"), "ddmmyy") & "," & Format(rsName("Time"), "hhmmss") & "," & Format(rsName("HFUnits"), "0000") & "," & rsName("Dialled Number") & Chr(10)
put #1 ,, strCDR
reads fields form a record
and concatenates them into string but seperating each field by a comma and adding a chariage return to the end of each line.
It then outputs the string to a text file.
The results, however, are not quite as expected:
the problme is the " - á" characters which prepend the start of the string and the "á" after the final comma but befor the final field. Where have they come from ?? They are not in the source table.
Any advice greatly appreciated.
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.
SELECT students.surname, students.name, students.father_name, students.father_prof, students.mother_name, students.mother_prof
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?
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 ..
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 received a very strange error this morning.
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.
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
Hello To All,
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:
- Charles Williams
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?
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.
EDIT: Found more.
The ISC query has the following field
StockOnHand <--- from a stock on hand query.
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
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.
Add Stock (from an alter stock form for discrepencies)
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.
Thanks in advance
I have a select querey
which contains the expresion
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.
The Problem that im getting is that when I query select a yes/no field in a table the results return TRUE and FALSE, even when the criteria states Yes.
does anyone know whats going on with it.
I know it isnt me as I have had my collegues check it over and they are all confused as well.
any help will be great!
i have a form with some control on it. Among these controls, i have 2 unbound image control, which displays linked images.
Now i am noticing that with this particular form only, if i start click the next record arrow in the navigational pane very fast to move through the records, all of a sudden the whole access database will disappear abruptly leaving .ldb file open.
Any idea why this happens ? Do you think that those 2 unbound image controls has to do with it, because if i try hitting the next record button extremely fast in other forms, it simply scrolls through the records, without the closing the database abruptly.
Many will look at this and laugh but its real.
My Access Dbs are opened in hidden state and i dont see my forms.
This must be the cause.
I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results
This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before.
This is REAL but can i get some suggestions from you folks before i repair my OS.
I am trying to add a normal JPEG image to an Access 2003 form through the "Image" control.
The problem is that when I browse for images , it does not show any JPEG images, but only BMP and images of other 3-4 formats - The JPEG format doesn't exist there. When I try to add a JPEG through "all-files" it gives me an error message - "does not support this format".
There is not problem viewing JPEG images on WinWord or other programs.
Has anyone heard of such a problem and its solution?
I am trying to use the runsql command and I am encountering a strange problem.
The following works:
DoCmd.RunSQL ("INSERT INTO financial VALUES ('" & clientNumber & "', " & txtFinancial & ", '" & firstname & "', '" & surname & "', '" & txtDate & "', '" & txtTime & "', '" & editdate & "', '" & edittime & "', Null, Null, Null, '12', 'Bob', 10)")
But when I change the Null to a textbox with a null in it, it wont work:
DoCmd.RunSQL ("INSERT INTO financial VALUES ('" & clientNumber & "', " & txtFinancial & ", '" & firstname & "', '" & surname & "', '" & txtDate & "', '" & txtTime & "', '" & editdate & "', '" & edittime & "', '" & txtacc & "', Null, Null, '12', 'Bob', 10)")
The textbox txtacc has a null value in it.
This is frustrating me beyond belief. Please help.
Thank you in advance
When I recently opened a Db I had been working on I received the attached Msg.
The path to the Db is C:Database ProjectsAEMSWarranties and Sales.mdb
Once OK is pressed the Db works fine.
I created a new Db and imported all objects, but no change.
I work in an environment with 7 computers networked to the same database. Until last week the database worked fine on all computers.
One computer was wiped clean and reinstalled with XP pro sp2 with all updates, office XP with all updates and also every other programs needed etc etc...
Now the database that every one uses will open up but will crash when the date filter is used in any form/report/query... This is the only computer this happens on and the error sometimes comes back with the following number 2147418113 but is intermittent. I dont think it is anything in the VB as it works totally on every other computer and worked fine on this one until reinstall of everything...
any thoughts anyone??...or has anyone ever heard of this??
I am getting a very strange ODBC error, when connecting to a SQL database.
There already was an MS Access DB, connected correctly to a SQL back-end. I copied this database, removed the ODBC links, then re-linked to the new SQL database correctly (or was done in the past). I could see the data correctly, but my users could not.
Test, test, test...
So we have come down to this situation.
I have used the new MS Access database (front-end) that was giving the ODBC errros. I then connected to the old SQL database, the very same way it was connected before. So, the users should still see the data. It uses the very same File DSN connection, to connect to the very same SQL Server, same database, and same tables.
For some odd reason, in the new MS Access DB, the users STILL get the ODBC error. Even though, the connection is exactly the same as another MS Access DB that works correctly for them.
Could something within the MS Access DB cause ODBC errors? Where I use the very same database/tables and DSN file. But the only difference is the MS Access DB?
I had a custom application created for me. Yesterday I received the compiled build to test. It worked fine.
For some reason, today I cannot run it. I get an error that says: "Connect to server, no such host is known".
My programmer is stumped. Do you have any ideas?
Thanks so much
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
Bit of strange one that I can't work out.
We have an annual leave database on a server (G: Drive) - the database is just a simple set of tables and one search form. Staff input a leave request via an InfoPath 2003 form which adds the info to the table in access 2003.
All users have access to both the database and the infopath form, the database used to have an Admin account with password attached but i removed this as users were prompted for it when sending the form.
All is well and good for all but one user - this user has all the correct permissions etc. but when they try to send the form to access gets a Windows 3.1 type box talking about Jet db and asking for an admin password but if i enter the password it will not accept it and if i delete the admin bit it still comes up as an error. All drives are on Windows XP and using Office 2003, box that appears is old fashioned type with drop down box for Readonly, Readwrite etc.
Strange one but has anyone come across this and does anyone have a solution?
I've never seen this before.
I have a form bound to a non-updateable query. There is an unbound combo box in the form footer.
When the form contains no records, the combo box display goes blank after you make a selection. I checked the After Update event of the combo box and the value selected is still intact but it will not display it in the actual box on the form. I've tried Me.Refresh, Me.Requery, nothing works.
If the form contains records it works just fine but often times this form may not show any records when it is first open.
Check out the attached sample DB. Can anyone explain this one?
I have a form, with some controls, a subform and some command buttons. In the OnKeyDown Event of the subform, i put the following:
If Me.NewRecord And Keycode = 9 Then
However, as soon as i hit the tab key when i am in the new record of the continuous sub form, rather than it moves to cmdAddSong, the focus is set to the control after it (cmdDeleteSong). Similarly, if i try to set the focus, to the cmdDeleteSong, after i hit the tab key, the focus will move to a next control (cmdAddAlbums).
Any ideas why does the focus does not go to the control i specified in the Me.Parent.Control, but it goes to Me.Parent.Control + 1 ?
I have two subforms on my main form. Both subforms are very similar and comprise two fields each. Both have an After Update event set on one field to enter the current date into the other field.
In one form I can update a record, press F9 and everything is smooth - I can proceed to somewhere else.
In the second form, if I update a record, the date is entered in the second field, but with black background, and I have to focus on the field, press Enter and then Esc (in the right order) to be able to proceed. I have tried many things to stop this as it is slowing data entry (just when I am starting to feel abit sleep deprived with an early start ahead).
Any suggestions greatly appreciated.
i have noticed some strange problems with comboboxes that occur for once in 20 times or so. When it drops down some rows are shown absolutely blank (no data at all) visually but they behave absolutely normally otherwise !!!! the comboboxes have autoexpand turned on which is a necessity for me. Is there any solution/work around ?
Okay, simple design:
Field: DesiredPercent - Primary Key
that's it, just a single field.
Of course, DisiredPercent is formatted to Percent with 2 decimal places.
ID : AutoNumber
Percent: Double - Source: Table/Query table_percent, boundcolumn=1
so on the form that has a combobox control linked to table_other.percent, you can select in the formated combobox which one of the list provided by table_percent, you'd like. However, for some reason, when I've selected the 95% it gives me this error:
run-time error '-2137217887 (80040e21):
you cannot add or change ar ecord because a related record is required in table 'table_percent'.
I've even done a quick DLookup() on the table in my VB code (since this update to table_other.percent happens via a ADODB.Recordset) and the DLookup("DesiredPercent", "table_Percent", "[DesiredPercent] = 0.95") returns Null
Why is this? I look at the table manuall and 95% is in the table, and when I select 95% via the table_other datasheet view, it works. but through this form method, i am somehow loosing the last record of the recordset, and 95% is no longer valid. Why, why, why? (yea i' m a bit frustrated on this one)
Jaeden "Sifo Dyas" al'Raec Ruiner
I have 2 tables, tblCustomers & tblPriceChangeAuth.
The database is for auditing purposes where by we have to be able to trace any price changes we make. The problem is as follow's:
CustomerCode e.g. 1000001
CustomerName e.g. Joe Bloggs DIY
PossibleCat1 e.g. All Independants
PossibleCat2 e.g. All Customers
The Problem is that a price change may be logged againts a customers name e.g. Joe Bloggs DIY (single Customer), alternatively it could be logged as an "Independent Customer" (multiplie customers) or it could be logged against "All Customers"
I basically want to be able to put in a customer code and a date and have the form bring back all the results of price changes that were effective on that date (all price changes have a start and end date) using the other 3 fields as the parameter.
i.e. It would return all results for "Joe Bloggs DIY" all results for "All Independants" and all results for "All Customers" is this possibe?
I have a bound form with a bound subform which is in continuous or datasheet view. I have been experiencing a strange problem of late. After i complete the first record of the subform and then move on to the second record , sometimes i get strange messages like :-
1) The Microsoft Jet Database Engine Stopped the process because you and another user are attempting to change the data at the same time.
2) Invalid Argument
3) Unrecognised database format c:windows empjet(something).tmp
4) Not a valid bookmark
These errors once they appear wont go away even if you try to ignore them. they just keep coming and each of the columns has an "#error# written in it. you have to close the module and start again. Whats more if you enter exactly the same data again, they do not show up again i.e. random errors. and it happens only when you complete the first record.
To clarify, these errors appear even in a single user environment. i do not change the recordsource or link fields properties at run time for the subform or mainform.
is there anything i have overlooked in form design / code ? Please help me out.
Well I used the search feature and nothing came up, so I'm going to go ahead and ask this. This may just need a quick fix but I've been working on it for over an hour and its starting to really frustrate me...
I'm creating a combo box for Market Segments (ie Business, Medicine, etc). I enter my values in the wizard and create the combo box, but when I am entering data and select a value (ie Business), "Business" is selected in ALL the other pages of the form. In other words I can't select "Business" on on page and then select "Medicine" on the next without changing all the pages to "Medicine".
Thanks in advance for your help!