I have tblClasses, tblEmployees, and tblRegistrations. I have a registration
form with EmpName and subformClasses, which allows me to register one
employee for several classes at a time.
My registration form has an OK button. I need a message to appear (on btnOK click) before adding a new record to the registrations table that indicates if the employee has already had training for the one or more of the classes selected in the subform. Then the option to "Yes" add the record anyway, or "No" cancel the record.
I have everything working except, I can’t figure out how to do the
comparison with the subform and generate my "Yes/No" option. I’m hoping that one of you wonderful access geniuses will have a relatively simple suggestion for me.
How do you compare one record to the previous record? I have a query that shows items sold. It is sorted by day with today on top. I would like to create a column that has a 'thumb up' or 'thumb down" if todays was a better day than yesterday.
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code: SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate() Dim dbs As DAO.Database Dim rstTest As DAO.Recordse Dim strQuery As Strin strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?
I have table A with a list of Work Instruction references (WIREF) and a Issue number (ISSNO).I have Table B wth employees and they too have a Work Instruction (empWIREF) and Issue number (empISSNO).I want to check if the Work instruction Issue has changed since the employee was trained
check: WIREF = empWIREF and ISSNO <> empISSNO for all records in table B
I have a TSQL procedure written which validates if a record already exists. It returns a 1 or a 0 depending on if it finds a valid record based off a supplied paramater (an application ID in this case, stored as Text(14)).
I want to accomplish the same thing but in a standalone Access DB where I don't have access to SQL stored procedures. Basically, here's what I have:
frmInputNew contains a text box txACAPS. When a user enters in a number in the txtACAPS field, and tabs out, I want an event to run which checks that string against a table. If the string exists within a field in the table, I want to break out and open a new form (which will be a dialog box). If it doesn't exist in the table, I want to end the code and let the user continue entering in data.
I'm not entirely sure how to check a table for a value via VB and return a flag that I can form an IF/ELSE statement from. Any help would be appreciated.
I have a form with information on it relating to several linked tables. I would like this information to be duplicated in the tables and a new autonumber assigned.
Is there a way that I can do this by clicking one button and the autonumber will automatically generate a new number keeping the rest of the information in the form and updating the tables with a new record?.
I have 2 forms, with a "PNID" field as the link between the 2. In the 2nd form, the PNID field allows no duplicates. I have a button on the first form to open the 2nd form to add a record.
I need some code to put on the button on the 1st form to check whether a PNID record exists on the 2nd form before opening it.......does this make sense? What is happening is that I click on the button, try and add a record, then it tells me one exists and I cannot add a duplicate...
I have looked at the threads, and have tried using DCount? Is this correct?
First Access 2013 form being created. (i may not use proper termonology but trying).
Tables: tbl_Clients (PK=clientID auto#)-holds details of each client (Lname/Fname/MName/addr/preferences etc) tbl_Encounters (PK=encounterID auto#)-could have many encounters for each client. tbl_followup (PK=followupID auto#)-some encounters need followup, some don't, some encounters have many followups.
Have a form linking to tbl_Client, with subform to tbl_encounters (going to create another form for followups-haven't got that far yet). I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new. However for the existing client if say the address changed i want them to be able to do that. (i could get rid of dups in a query/report based on matching certain fields etc, however i'd like to avoid it in the first place).
Hi all. Using Access 2003 I am trying to figure out how to print and collate a report and an existing pdf file that is opened based on a field in the record. I figure that it probably has to do with looping through the records but I am not very good at that. So I have a form with a sub form that opens records based on a combo box. I then have a report (a checklist sheet) that prints 1 page per record. I would like to print a pdf file associated to that record before it prints the next record.
Here are the names of the objects being used: tblMoveDoc frmMoveDoc subfrmMoveDoc rptCheckList
I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.
Here is my save function code:
Function SaveData() As Boolean Dim db As Database Dim rs As DAO.Recordset
Set db = CurrentDb Set rs = db.OpenRecordset("Skips Delivered")
I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.
What event would I attach code to, to have a message box pop up warning the user when that he is editing existing records.
I would like the message to appear not as the form loads, or as he tabs to the first control, but right as he makes any changes to the data displayed. Preferably the box should have a "OK" to continue the edit and a "Cancel" to undo it.
I'm successful in adding data from the form to the table using VBA. But I couldn't retrieve and edit the data. For retrieving and editing the data I have a different form "[Forms]![Editor]". I have all the fields listed in the image in both the form and table.
1. I want to allow the user to search and retrieve a specific row based on the condition Incident ID and the Package code are same 2. I want the user to edit the fields in the form and update it in the same row again.
I have a database where I need to append part of a new record to another table. But when I open the query with the link to the form that is open I cannot see the added record until it's saved to the open form. I tried to re-query the key ID field but that does not work. How do I do a re-query without the form requiring everything and then I am on the incorrect record?
I want to open an existing record via a form and then edit that data but save the form as a new record. Basically a form used for handover/takeover process where most of the data remains the same but the new form is the basis for the next handover/takeover.
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA Associated LOTO/s in tblLOTOs 25258 123456 123457 123458 123459 25259 123410 123411 123412 123413
However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate() Dim db As Database Dim rs As Recordset Set db = CurrentDb
So, I have two forms that I am trying to make work the same way.
With F1Entry I can use the combo box in the header to select different request numbers.
With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset Then it uses the rs.Findfirst method It checks if rs.Nomatch is not true (so the records exists!) Then it starts updating the record with rs.edit rs!value1 = myvalue1, rs!value2 = myvalue 2 rs.Update There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
