I am wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple. I tried
PHP Code:
CurrentDb.Execute _ "DELETE FROM tblRecommendedProducts " & _ "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError
I have Table for rooms called Rooms, and the data in the table is roomNumber which is in the format Letter and two Digit number, so A01 would be dorm building A and room building 1, and I then have a seperate row named roomType that is either VIP or Semi Private.
Now I am creating a form where a worker will put in there scheduledCheckin date and scheduledCheckout date and it will be written to the Bookings table. I would like this form however to take the dates they have put in, as well as room type (Semi-Private or VIP, and assign them an available room that isn't booked at all in that range) or list all available rooms for that range of time and they could then just select the room. I would rather it automatically assigns an available room based on room type though because this check in system is going to have about 500 rooms.
I've attached a link for what I have so far. I know how to make a query to list anyone who has booked rooms over that date, but need one for just preventing booking the same room.
[URL] .....
I currently have a listbox. (list32) that is populated with results of a query. I would like to doubleclick on an item and have it open a form that displays the listbox record i clicked on and a "notes" field. Its my understanding that the table fields are limited to 255 chars so i was curious if I could make the notes field reside somewhere else. I will need the notes able to be edited, specific to record entry, and then saved on exit. Is this doable?
View 2 Replies View RelatedI have a mainform named EMA and a subform named Q_EMO. In the mainform there is one event related to a patient.In the subform Q_EMO there are all the events releted to the patient.I would like to get a record in the mainform EMA when I double click in the record of the subform.
View 12 Replies View RelatedCurrently i have a form with subform datasheet
Form from Purchase order table
Subform from serial table(Brand,Model,Type,Location,Serial) with serialtrans table(InOut,AQty,Comment)
(SELECT tbl_Serial.*, tbl_SerialTrans.*
FROM tbl_Serial LEFT JOIN tbl_SerialTrans ON tbl_Serial.SerialID = tbl_SerialTrans.SerialID
when i click on the datasheet row and delete the row it only delete the record from tbl_SerialTrans but record on tbl_Serial was not deleted
is there any way to delete it?
I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:
Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.
Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.
When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.
Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this
I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:
Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database
1. Complete = False AND
2. Supplier on form = supplier in table AND
3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"
I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:
Me.cboSupplier.RowSource = "SELECT DISTINCT [Consolidated_Master_Req_Pool].[RFQ Contact] " & _
"FROM Consolidated_Master_Req_Pool " & _
"WHERE consolidated_master_req_pool.Complete = FALSE AND [Consolidated_Master_Req_Pool].[RFQ Supplier] = '" & Nz(Me.cboStatusRFQ) & "' And [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RFQ FOLLOW-UP" & "' OR [consolidated_master_req_pool.Status] = '" & "SUPPLIER_RE-RFQ OTHER SUPPLIER" & "'" & _
"ORDER BY [Consolidated_Master_Req_Pool].[RFQ Contact];"
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.
So I found that some of the ZIP codes are either blank or have less than 5 digits.
Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.
I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
Apologies, this was posted as a reply to a previous thread I submitted but its not related to the title anymore (dlookups).
I have a query that looks up a payrate value for an employee in a payrate table. It uses the appropriate value to determine the pay rate at teh time an employee has worked (DateWorked). On top of this I also need to introduce a parameter criteria on the DateWorked to allow the user to run the report for a time frame. This is where I get stuck as I cant seem to get it to work properly if I introduce another criteria on the field
The query that works great (without the second criteria) is as follows;
SELECT tblTimeCard.EmployeeId, tblTimeCard.TimeCardId, tblTimeCardHours.Task, tblTimeCardHours.Hours, tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.DateTo, tblPayRates.PayRate,
tblTimeCardHours.Hours*tblPayRates.PayRate AS Cost
FROM (tblTimeCard INNER JOIN tblTimeCardHours ON tblTimeCard.TimeCardId = tblTimeCardHours.TimeCardId) INNER JOIN tblPayRates ON tblTimeCard.EmployeeId = tblPayRates.EmployeeId
WHERE tblTimeCard.DateWorked Between [tblPayRates].[DateFrom] And [tblPayRates].[DateTo];
This is where I need to introduce another criteria where tbl.TimeCard.DateWorked Between [Enter Date From] And[Enter Date To]
when I try this I must have one of the joins wrong as I get all the payrate values for each date worked even if they are outside the dateworked criteria set by the second criteria.. I hope I have explianed this ok. Can someone please advise where I should be looking - many thanks
I currently have a database which contains various information for part returns. Among this information is contained a parts tracking number, VIN Number, and Date Code. I want to have a small dialogue box (Form) where a user can enter a tracking number, (or a date code or VIN if tracking number unavailable) to search for a record containing that tracking number. I have already created a replica of my new part entry form to view parts in read only mode only.how to create small form to search for record based off criteria then open the replica form i have made to that record?
View 6 Replies View RelatedI have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
Hola everyone!
I need to know how to delete dates using the criteria field in an delete query!
This is how the table looks:
Field1
Smith, Mike J
09/26/2005
11/16/2005
The date range is wide, so doing a between is not a good option.
Field1 is filled with information like the above. How do I delete the record with the dates from this field? I tried using a Like, but was unsure on how to represent the (2 spaces/) the 1st three characters.
Can anyone help????
!Mucho Gracias! In Advance!
I am new to access and I recently encountered a double click issue
My form loads perfectly on double click event but it shows the first record instead of selected record.
My search is based on a PersonID but each PersonID has different WorkID that I wish to display on double click but it always shows the first WorkID instead of my selected record
I have tried changing the filters in the form properties but it still doesn't work for me.
Here's my code:
Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "WorkForm", , , "[PersonID]=" & Me.[Searchresults], , acNormal
End Sub
[Searchresults] draws information from my Query
Query information:
PersonID... WorkID... Type......Location
1234..........1............Paint .....Address A
1234..........2............Electric...Address B
1234..........3............Floor..... Address C
I have an existing database with multiple tables. Two tables have the same primary key Cab#.
1. When user clicks on the form on the menu form, I want to request the Cab# value from the user.
2. Then I want to get the record from the first table CabData based on the entered Cab#.
3. Then I want to create a new record for the second table Rides with five fields from CabData table inserted into the Rides record.
4. Then I want to display the new Rides record to the user for updating.
5. Then I want to save the new record to the RIDES table.
What is the ACCESS termonolgy to do each step? Are there any special details I should watch for?
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
I'm creating a database for my inventory. For now, my database have two tables namely tblSupplies and tblDetails. I have a main from (frmMain) and a subform (subDetails). On the main form i have a combobox (cboSupplies) and 5 textboxes, the cboSupplies and the five textboxes are unbound. If I select a value in cboSupplies it will populate the rest of the textboxes-OK.
What I want to accomplish is that when I select a value in cboSupplies it will grab all related records from the tblDetails and populate these records on the subform (subDetails).
SuppliesID was used as the link field from mainform to subform but its not working. Please note that SuppliesID field in tblSupplies and tblDetails has a One-to-Many relationships.
Hello All,
I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.
I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.
I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.
I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.
Dim rst As Recordset
Dim strSearchName As String
Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True
Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName
CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError
Me.Requery
Me.sfmOrderDetails.Form.Requery
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Thanks!
I have a main Form "Client" that shows details such as Representatives, phone numbers, status, etc. I also have a main report "CRM" that is a nicer summarized table version of all of my Client form entries.
The "CRM" form will be used by others in my office to see what clients we are contacting, etc. What I want to be able to do is have someone open the CRM form first. If they want to see even more specific info (not everything is included in the report, as that would be too messy), I want them to be able to double click the Client's name on the report and be taking to the corresponding entry on the Client form that shows more details.
Right now my code for the Report which is not working is the following:
Private Sub Client_Name_DblClick()'double click on a client name in the reportDoCmd.OpenForm "Client", acNormal, , "[Client_Name]='" & [Client_Name]'this should open the Client Form to the record of the same client that was double clicked on the reportEnd Sub
Using Access and Outlook 2010. I have a database that tracks Contracts for our University. One of the fields it tracks is Expiration Date.
Is there a way to have Access prompt Outlook to send an email to my boss when the contract is a month or within a month from expiring?I'm assuming when the database is opened it would have to run a query for expiration dates expiring within one month of today's date (which i know how to do).
I just don't know if it's possible to generate an email or some kind of message with those records/contract numbers displayed? I should also note my boss is the only one who uses this database so even if the email notice isn't possible, is there a way to show a message of what contracts are expiring soon when he opens the DB?
Is it possible, to input information from my continuous form into a web control form. at my job we a required to tract our jobs by equipmentid and job control number(jcn). When a job is done we have to upload the id and jcn into a website to tell it is cleared. I am looking for a way for that info to automatically be filled in when i load the website based on the job i have selected in my form?
View 1 Replies View RelatedHi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.
I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.
Sub DeleteRecordButton_Click()
'Delete the selected record
dim strSQL as String
strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]
DoCmd.RunSQL strSQL
MsgBox("Confirmed")
End Sub
Thank you!