I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results. after item 2, do an export. etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt, [a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
I have two table. Deliveries Created (DC) and Goods Receipt (GR).
I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.
This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.
There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.
I will explain this situation using a scenario. Let's assumed that i'm working on a library system where i need a loan form that will only show books that are available for loan. In this case, this form has a main form that shows library member's details and its subform shows the details of the books that are loan by library member. In this subform, one of the fields (ISBN no) displays its values using a combo box. Once a value is selected from this combo box, values in other fields found in the subform will be shown too. These values are based on a table that contain books info, where only the value (ISBN no) in the combo box comes from a query. This query will only show books that are available for loan.
If a library member wants to borrow 'booktitle1' then this book will not be available to be loan by other members and assumed that there is only one 'booktitle1'.
I tried to update the field manually by changing the status field every time the book is loan out. I hope to solve this problem in an effective way.
I am new with access and i have difficulty to explain it in a much better way. Sorry, if my description cause any sort of confusion.
These are the tables.
book_info (table1) ------------------------------- bookTitle ---------- text ISBN(pkey) ------- text authorName ------ text category ---------- text dateReceived ---- date publisher ---------- text status -------------- text
user_info (table2) -------------------------------- name --------------- text userID(pKey) ------ text address ------------ text tel ------------------ number hp ------------------ number occupation -------- text DOB --------------- date
loan_info (table3) ------------------------------ userID(fKey) ------ text ISBN(fkey) -------- text dateReturned ---- date dateBorrowed ---- date dateDue ---------- date remarks ----------- text
I have an user imput form that has a unique file reference number which is typed in by the user in the first bound field. Does anyone know of a way of informing the user that the file reference number is already in use and therefore a new one must be used. At the moment the user is completing the entire form before being made aware that it would create duplicate records and therefore unable to save current record etc...Totally fustrating! any ideas? Steve
I have a database that lists all the books I have read. The Table "Books" holds "AuthorId' "Title" etc. I have another table that is called "Whats Next To Read" which I store the next book that I will read from that author. It also uses "AuthorId" and "Title" etc. In the form, how can I look to see if the title put in for that author in "Whats Next" does not have the same title already in "Books" table. I can use Dcount to find the title but it could be same title different author. I need same title with same author.
This is what I have so far.
If DCount("Title", "Books", "Title = '" & forms!Whatsnexttoread!Title & "' > 0 Then Msgbox "Title is already in books.", vbInformation End If
ok im having some serious problems here. im using a tutorial to make a database for my small business and everything was going great, except this 1 problem ive been having for like 2 weeks now. i had my 2 main tables linked together using another table which is my foriegn field i think? anyway i used a query to make this table and to add the data from my other 2 tables to it. now i want to add the data from my suppliers table to it aswell but i dont know how. my original query wasnt saved cause the tutorial told me i didnt need to save. i think what i need to do is append this data to query like i did when i first made it but if the query isnt there anymore how can i append it? does this mean i have to start my database from scratch and do all 3 tables at once? or can i make a new query? ive tried making a new query to add this data to the linking table but no go. im horrible at explaining this so hopefully some pictures will help.
heres my relationships. what i want to do is make my tblSuppliers link to my tblItems. i think i need to link it to tblCategoryItems and do the query thing to add the data to it. is this correct? or is there another way i can do it?
in this pic you can see how the first 2 fields were populated for me by the query but now i added an extra field which i want populated with the supplier ID. i tried to manually add the number of the supplier here but it didnt work either. i think i need another primary key for it to work but i cant add another primary key to this table cause access complains.
Can you append to a table that is empty? If you can please let me know. I made table through "make table" query and now I run the append query to the empty table but it is not putting any information in the empty table
I need to build up a string containing values separated by commas. The following almost does this.
I use an update query in which the "Update to" field for 'String field' contains [String field] & IIf(Len([String field])<1,"",", ") & [Related table]![Value to append]
I would like to have the [Related table]![Value to append] in ascending order. I.e. I would like the result to look like "A, B, C, F, H", not "B, C, H, F, A". Any suggestions as to how to apply the sort?
i have several comboboxes and textboxes in a form called inputorders.frm. I am trying to get all of them to an append table which would allow to show the control values in a table on the form with a command button:"add item". I'm using a append query that will only run by itself. Getting the "enter parameter values" msgbox and not the values from the form.
I really don't know what i'm am doing wrong. It is a single row and I can't get the appendquery to work with the command trigger event
What is the correct way to code the query along with the command button to trigger the query with the control values automatically displaying in the table.
i have a table with tons of records in it. There is a field called "date to return by" that previously had a default value of Date+30. Now, some things changed in our company, and under certain conditions, the "date to return by" for new records will be Date+14, or Date+30. At first, i tried making this relationship in a table as a default value, but, you cannot make a relationship like this in a table. So, i made a query to evaluate the relationship. what i want to do, is have a query that will have all the same fields the table had and evaluate whether a records' "date to return by" should be Date+30 or Date +14, and then put that new record back in the table.. but, this will only be for new records, as i will be keeping all the old records in the table as they are. how can i do this? all of my reports read off of my table so i want to keep the table. can i make a query that will put new records into a table one at a time as i make them?
Ok so I am making an application which requires me to add a record.. ok fine, but then i also need a cancell option, which would delete the record... np, BUT if the user cancells, I don't want my Auto number increasing... So I am writing to a temporary table which mirrors the original one, and then if the user clicks cancell, i delete the temporary record... If the user clicks "save" then i want to Append that new record to my original Table and then delete the record... HOWEVER, when i appended this record the first time it added 2 identical records to the original table, Then 4 the next time, Then 8, 16, 32 , etc etc I'm not quite sure how I did this but i hope its a common problem that somebody has maybe done themselves and will be able to help me out...
Attached is a Skeleton with the original Table, and the Temporary Table, along with the same query currently i have some data entered, When you attempt to add another record to the temp table , and then run the append query, it will add 8 identical records... TRY IT! and then HELP ME!! please!
I'm making a database for tracking purchase orders for contract workers in my department. Business rules dictate that a PO is created for each unique Contractor-Project instance.
I pulled a report (.csv format) of weekly timesheets from the company's project management database system. Some of the fields include: EmpUserName, EmpID, ProjectID, ProjectName, WeekEndDate, Hours. I linked to the file in my database and ran a query on it without WeekEndDate so that I get the total hours each contractor has worked for each project they've billed to. I created a table called tblContractorProject and appended the records to it. I'm going to be pulling this report each week so I can see if any contractors start billing to any new projects so I can create a purchase order for it. Is there a way to write an append query that can recognize if the EmpID and ProjectID combination is new and doesn't already exist in the table?
Hi. I have two tables, both with load of info. I need to run a query, that checks data in table1 against data in table2. If it is in table two, then moves on. If it is NOT, then shows the results to me. So that i can see which ones were not there. how do i go about that.
I have about 100 or so tables that I need to append back into one table :eek:
However all of these 100 tables all begin with the number 100 at the beggining. I know that you can append tables - but as far as I know you can only do one at a time. Is there any easy way to do this - for example writing a small SQL statement - saying select all tables that begin with 100* and then append into a master table :confused:
Alternatively are there any programs on the net that can do this (I just wrote a massive macro to import them from a text file in to access- not realising this problem would happen!) :(
Unfortunately each of the tables has the first row as the column heading too?
I have a local table that I am trying to append to a linked table. The fields are exactly the same. When I try to append the entire local table to the linked table I get an error code.
ODBC- insert on a link table failed. [ctreeSQL]-17002 CT- Key value already exists in index (linked table field) (#-17002)
If I specify the criteria in the field to refer to a specific value in the local table, it updates it just fine. I want an append query because I don't want to manually update 500+ records!! I don't believe an update query would work because the values are not in the current linked table... so nothing to update!
I am trying to implement an update query per the instructions at http://support.microsoft.com/default.aspx?scid=kb;en-us;127977 to append records from table2 to table1.
I would like to add a criteria here saying update (and append) only records that match the criteria table2.dateofreview <= #12/17/2006#
This does not seem to work. The same query works without this criteria.
To test this, I created just one record in table2 (table1 is blank at this time) and manually entered the dateofreview as 12/17/2006 and tried to execute the query. It is not able to show any records.
continue search, 'Duh' to stop", vbExclamation + vbYesNo, "SearchInQueryDefs") Then Exit Sub End If End If Next qdf
MsgBox "Done searching.", vbInformation, GetAppTitle() End Sub
Using the following statement (in the immediate window) i get the following result: SearchInQueryDefs "Queries" Searching : ~sq_cfrmReports~sq_clstQueries... - found However query "~sq_cfrmReports~sq_clstQueries" doesn't exist. It is probably a query which populates the listbox "lstQueries" in the "frmReports"
form. But that form doesn't exist in my database. I have deleted it some time ago. I
thought that Compact and Repair got rid of stuff like this. I found the definition in the MsysObjects and with this Id also in the MsysQueries. So my question is obvious: what is this, why is this and what can i do about it?
Hi everyone, I'm nearly done finishing a database for a legal firm and one of the small issues I'm having with this project is that, I have a button that creates records in other tables once a record is created in a primary table.
I have a table called tbl_login, it has a field called job_number (which is an autonumber), and when all the info is entered in the form associated with tbl_login I want a button to give the user the ability to create a record in tbl_billing with the value of job_number, now this is a easy append query, works great, but the problem is, when the button is pressed there is an event message, "You are about to update an table....." this is the natural warning even message that access gives before a change is made, my questions is.
To please me end users, how do I get that event message to stop popping up. So they just hit the button, it appends, no warning.
I am a new user to Access. I created couple of forms using wizard, but when I view them, they show me all the available data by default. I want to stop this, if anyone can help me plz.
