right all tables are like a 2d array so does any body know how i can access a field in a row of a table ie like in most languages it goes a bit like this ArrayName(1,1) with ArrayName being the name of the table and that access the first field of the first row. im new to Vba and access 2000 but all i want to do is access the fields in a column of a sub-form displayed as a table add them all up and place the answer in a text box on the main form stick all this in a function so i can run it inside a macro
Can anyone please help or advise where I can look for help on this problem. I have a subform which shows all my outstanding orders. Below it is a calculated field which shows the total weight of all the orders. (Steel Beams) I would like to be able to highlight the weights on certain orders & show total weight for only these orders. (Similar to the way it can be done in excel) I have been trying to do it via a query with a Yes/No check box & when I check the orders required the total weight changes. But I cannot get it to work properly as this puts a Yes in the field & the record disappears. I only want it for a quick reference to save doing mental summing up so don’t want to change or add anything to a field to get answer. Can anyone give me any ideas please?
I have four feilds and im trying to subtract them in a query
This is what the SQL looks liek for two feilds. but i have 4, i was gettign and error when i added other feilds Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT]),"0000000000") AS [Form Total], im trying to subtract these feilds Here are my feilds Allowed-deduct-other-Coins-Cob
Hi every body. I have an access database that u can see the tables and their reletionship in the link i posted. I would like to web enable this db and would like to get some suggetion on what fields should i allow to be edited and what records should i put delete option for? In order to keep the refrential integerity of the acces db.i be happy to get some feed back from u guys.Thanks
I'm making a test database for DVD rentals. When A loan is made I have an update query that sets some fields in the DVD copy table. One of these fields needs to be set to the primary key of the loan made. This would be the last entry on the loans table. I don't have any idea what to put in the 'Update To' box in the query though.
Wrong code here, but this may help explain what I am trying to get: Tables.Loans.LastLoan.Loan ID
Also heres an image of my query: http://i92.photobucket.com/albums/l15/mikegrundel/Accessproblem.png
:rolleyes: hi i am raza. i am facing a problem while accessing the properties page of access table. when i gets its properties page it has no properties on it.i have attached the pic wich can explain the problem. pls tell me if any body can solve this problem. thanks. raza
I have developed a small database with 3 or 4 forms which works ok on most PC's. But when I put it on a certain PC, it almost works properly except for one form which is very slow accessing the table! Eg. It could take 2 minutes to use a combobox.
All PC's have Access 2000. Would there be a reason for this? Is this an access problem or a PC problem? Are there special settings that I'm unaware of?
Hi folks, I have an Append Query (Insert Into...) that I use to insert values into a table from a form. But to have that tie into other tables I need to gather the Student_ID (primary key) that is autogenerated when the record is inserted. How do I do this? I want to store it in a public variable. I've done quite a bit of searching but nothing of any value has turned up. Please help.
What I have on button press: DoCmd.OpenQuery "Register Camp Students"
After that I need something to gather that newly inserted Student_ID and store it for later use. Please help!
I have used and maintained Access databases in the past but this is my first experience with building a new database. I started by borrowing every Access book my Tech department has on hand and going through them. I have gotten to the point where my tech department can not help me because my question is beyond their own knowledge. I have searched through many different website’s forums and have failed to find what I am looking for. It very well may be out there and I am using the wrong terminology in my searches. The most important piece of information to be stored in my entire database is of course the one that I am having the hardest time figuring out how to set up.
I have a table called “Document Index” which will be a master log of every document that passes through my department. This table has the three fields which are important to my problem/question. 1. The “LOC” field represents the location at which the document was logged. The “LOC” field has a working validation rule that only allows a “D” or “S”, representing the department head’s office or the site office. 2. The “DUO” field value represents the three digit unique number assigned to a project by another department (accounting) 3. The “ID Number” field is a unique number we will assign to each document that is this table’s primary key. It is also where my problem sits. The “ID Number” format has been decided upon by my boss and the rest of the office has already begun stamping these ID Numbers onto every document (which I will have to go through and enter once the database is complete). I am stuck with the format as it is shown in the example below. “ID Number” format is: value of “LOC”- value of “DUO”-number
I know what I want the field to do but I have no idea how to turn my ideas into the proper code to see if it works. So here is my idea of a solution with example values entered. A.Fill in form for “LOC” and “DUO” B.At this point Access would run a behind the scenes search and return all ID #’s that had a that “LOC and “DUO” C.At this point Access would run a behind the scenes search through only those records found in step B to find the highest “ID Number” previously assigned D.At this point Access would make a calculation: adding one (+1) to the “ID Number” found in step C E.At this point Access would insert the result found in step D into the “ID Number” field of my current entry (which until this step the record only contained the “LOC” and “DUO”)
For example: if my first 4 entries had this info: LOCDUOID NumberDocument Description D410D-410-1 Wendy’s Ransom Note S410S-410-1 Priate's Demands D415D-415-1 Cinderella’s Birthday Invitation D410D-410-2 Peter Pan’s Response to Ransom Note
Then I complete step A by typing into the form: LOCDUOID NumberDocument Description D410D-410-1 Wendy’s Ransom Note S410S-410-1 Priate's Demands D415D-415-1 Cinderella’s Birthday Invitation D410D-410-2 Peter's Response to Ransom Note D410
Access would execute step B finding: LOCDUOID NumberDocument Description D410D-410-1 Wendy’s Ransom Note D410D-410-2 Peter's Response to Ransom Note
Access would execute step C finding: LOCDUOID NumberDocument Description D410D-410-2 Peter's Response to Ransom Note
Access would execute step D finding: D-410-2 + 1 = D-410-3
Access would execute step E: LOCDUOID NumberDocument Description D410D-410-1 Wendy’s Ransom Note S410S-410-1 Priate's Demands D415D-415-1 Cinderella’s Birthday Invitation D410D-410-2 Peter's Response to Ransom Note D410D-410-3 Then I would finish entering the information for that record.
I would appreciate any help you can offer, even if just pointing me in the right direction or correct terminology for my searches.
I have a query with below formular which return's all records between Sunday and Saturday
Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7
I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week. I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time. What am I doing wrong ???
I've got this simple thing.. which I couldn't do and stuck for a long long time.. I have done some programming in a few languages, but not in SQL or VBA and I quite confused about programming styles that VBA and SQL accepts.
I'm currently working in Microsoft access and the thing I'm stuck on is retriving field value from a record and writing into another record on the same table.
For example the table "Table-users" looks like the following:
ID Name Year Month Date 1 Steven 1979 9 18 2 Jeremy 1977 10 22 3 Eslynn 1988 10 10
I've built a form that contains two lables (combobox), "ReplaceMe" "LoadMe" and a command button. Which end user could choose which record to load and which to replace.
I wish to copy Year, Month and Date from LoadMe into ReplaceMe without effecting the Replace me ID and Name.
The VBA, SQL code i've got looks like. ----------------- 'Command_Click is the tigger button to active the following code. Private Sub Command37_Click()
'Below is an example of what I wanted to do field by field. However, in the real 'environment, I needed to move about 20 odd field into another object. I'm not sure 'to use Append or Update Query Dim UpdateSQL As String Dim text1 As String Dim text2 As String Dim text3 As String
'this loads the string containt-the Name of the object-into text1. 'this line works fine, arr.. other ones don't....... text1 = Me!ReplaceMe text2 = Me!LoadMe 'Load the month of the LoadMe. text3 = Table![Table-users = text2].month
'Loads the SQL instruction into UpdateSQL 'Table-table is a table, containing several objects and fields. UpdateSQL = "INSERT INTO [Table-users].text1( [Day], [year], [month] ) " & _ "SELECT Table-users.Day, Table-users.year, Table-users.month" & _ "FROM Table-users" & _ "WHERE (((Table-users.Name)='ReplaceMe'));"
DoCmd.RunSQL UpdateSQL
End Sub-------------------- Well the obvious, the above code doesn't run. If LoadMe = Jeremy, ReplaceMe = Steven, the above code should return:
ID Name Year Month Date 1 Steven 1977 10 22 2 Jeremy 1977 10 22
Making Steve's Year, Month and Date field the same as jeremy
======================
What I think i'm confused about is when do I use [ ] ( ) ! .
Like... if I wanted to read the field value of "Date" from the object from the "Name" in combo box "LoadMe", what should I write?
ie, if the combo box "LoadMe" contains Eslynn. The end result should be 22.
I have a database with a number of tables 2 of which are:
# Personal Members Table (with all their details and addresses and contact details) # Organisation Table (including who are members of this Org)
My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)
I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...
In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?
I have a set of maybe 5 template word docs which I populate with info form our database using bookmarks , the word docs are currently sat in a shared network drive.
I have started wonder if it would be better to store them in a table and call them from there to keep things together.
I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.
Here is what happens.
I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.
I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.
I will include the union query here in case.
The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.
I'm building a DB where I have multiple users. I would like to have all data in 1 table but only give access to records created by the individual user. Is this possible?
Alternatively, if each user has it's own table, how can I report on the sum of all tables.:confused:
Is it possible to access a table in one Database (Database A) from a separate Database (Database B) and if so how. I should clarify that this is an Access Database.
database is on laptop, the pc downstairs can open and access the db fine, but the pc next to it upstairs can see the db but gets an error on opening - of course they are using a german version of access so im not entirely sure of the error message but its basically "cant open the db on this intranet, copy it to your pc and then open it"
all pcs are running xp and the same version of access (2002) the db is in the laptops shared docs folder one pc can open it fine the pc in question can open and edit a word doc (for example) in the same folder but not the mdb i can map the drive on the pc as well
it seems more of a win xp file permission thing but the pc can open and edit a word doc ok
the only thing (seemingly) different with the 3 pcs is that the problem pc is in english and the other 2 in german (but why that would matter i dont know)
i'm supposed to be the pc guru but this has me stumped - any ideas ?
I got a little problem here. I have a mdb that must be moved to another pc, but whenever I try to open it (on the other pcs) I get the message that I don't have permission to read. When I try to change the permissions it says that I must be an administrator to do that, but I am the pc administrator, so I guess it must be the mdb's administrator. Does anyone know how to change the permissions, or how to make me the program's administrator/proprietary? Thanks for your time people!
I have copied a database that I used for another project for a new one. Everything is the same but when I try to use the command buttons on the form it give me "no record found" error, even though there are records in the database. I know that the Error message is the one that is writin in the database by the orginal designer, I can't quite figure out how to fix the problem I think that it has something to do with the VB coding in the comand button, becuase of the type error message that I am geting, but I don't know how to do it. I can't post the database becuase it is way to big. Everything else is working fine. What hte command button is supposed to do is update a total score percentage based off the numbers that placed in the database also any of the score can be left blank as to not count, max for each score is 5 min is zero, i.e.score1=3score2=5score3=5score4=score5=4score6=3Total Score =80.00%I hope that this makes sence.J
I have time entry program that the company I work for recently purchased that uses a SQL server 2000 Back end. I can connect to the database just fine if I create a project in Access. What I need to do is take data out of a couple of the tables in the SQL Server and place them in an access table. I really don't want to add any code or tables to the Access project that I created since it seems it would add tables and queries to this products database. What would be the best way to siphon data out of this software package for my own use? This would become a daily process so it needs to be fairly automated and easy.
Ok, Let's say I go into File->Database Properties from the "main" Access screen. This brings up a window with the title "<database name> Properties" where <database name> is the same as currentproject.name.
This window has several tabs including "Summary" and "Custom." It looks like this should be stuff that I can set and then reference from within my code.
My thought is that I can use the "Title" from the "Summary" screen as a variable for all of my msgbox titles. The other interesting one is "Project" on the "Custom" tab. It looks like this may be the same sort of thing.
I've found that the Access Help is absolutely useless - I'm about ready to take a shotgun to "Clippy." But as they say, a display is a terrible thing to waste <grin>. I've also found that it's getting harder and harder to find what I'm looking for in the MSDN library. There's so much stuff and they don't always file it in logical places.
Well, thanks for reading.. sorry for the rant. As usual, any help is appreciated!
I've got a back end database on one of my computers and i'm trying to get the link tables to work over a network from another computer. Ive set the link tables up but it wont let me edit any data because it is read only. Its not read only when i run the front end from the computer that stores the back end. How do i enable the front end to edit the data over the network?
also, it is a multi-user system so i want multiple front end parts accessing the one back end but whenever i try and access the data from one system when another is already open it says the file is in use and i can't do anything!
I'm really stuck at the moment and i really need some help please! :confused:
I am making a database and after a small amount of progress I save it and then copy and save with same name but with an extension V1, V2 etc. so that I can keep a working one as backup. Throughout this process I have come across 'Error accessing Network' connection may be lost.....! I have to go back to my previously saved version and redo the changes I was making. This time I didnt save until I was about to make my usual backup and I had quite a few changes. How can I stop this happening and make my last DB work again.
