I have an Equipment Form with a Supplier subform. The Supplier subform also has a subform, Equipment Pricing Records.
I have the ability to change the Supplier via a combo box, but Equipment Pricing Records become orphaned.
What I would like to do is when a Supplier is about to be changed, for the user to have the option to either a) delete the sub records, or b) the sub records to move across to the new Supplier.
I know I will need to use the after update event once the delete or move decision is made, but I can�t think how to identify the records and then either delete or move them to the new Supplier ID.
The EquipmentID will never change.
Fields are;
EQUIPMENT form;
EquipmentID
SUPPLIER form;
SupplierID
EquipmentIDFK
PRICING RECORD form;
PricingRecordID
SupplierIDFK
EquipmentIDFK
I am building a tool rental data base.I have places a delete button on my Transfer form so the operator can erase any mistake that he/she just made on a Transfer ticket.When I push the button I get an error message saying that I can't delete the record because it is associated with 2 tables. I have one table the tracks the transfer and another that tracks the tools associated with the transfer.How can I delete the transfer and the associated tools records?
I have a columnar form with a sub-form based on an updatable query. I've created a delete button on the master form with the intention that if the user hits the delete button, it will delete the current record (like its supposed to, right?).
But what is happening is when the delete button is hit, it is asking twice for deletion confirmation and deleting the current record AND the next record. Even if I hit "Cancel" to stop the deletion, it still deletes the current and next records.
Each record has a primary unique ID that is included on the form (but not tab stopped - if that makes any difference), but it doesn't seem to be using that as the basis for deleting.
I've tried two different codes for this:
Private Sub Command110_Click() On Error GoTo Err_Command110_Click DoCmd.RunCommand acCmdDeleteRecord Exit_Command110_Click:
I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.
I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :
Private Sub Command30_Click() On Error GoTo Err_delete_Click Dim stDocName As String Dim intResponse As Integer intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team") Select Case intResponse Case vbYes CurrentDb.Execute _
I have a form to enter archaeological features in a feature table. Each feature is present in one or more archaeological strata, for which I have a stratum table. There are 41 strata in total named 1A, 1B, 2A etc. On the form I want a checkbox for each of these and then to make it add a record to the stratum table for each of the selected strata. The stratum table and feature table are connected by the feature_primary_ID (key in feature table), so when making a new record I need this primary ID and the respective strata to be entered in the fields.
At the moment I've got something like this:
Code: Private Sub Check1_Click() If Check1.Value = True Then DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES ((feature_primary_ID of present record),Label2.Caption)" Else DoCmd.RunSQL "DELETE FROM tbl_FEAT_STRAT WHERE feature_primary_ID = (feature_primary_ID of present record)" End If End Sub
For starters, I dont know how to get the feature_primary_ID from the features table (also represented in the form as a field) and use it to make a new record in the strata table.
Second, when using this code I need a copy for each of the 41 strata checkboxes. Creating lots of redundant code. Easier would be a generic code that loops through all the checkboxes after they've been checked and then add new records for each of the checked boxes. Tried something with a command button, but couldn't get it to work.
I am trying to create a command button that will delete the current records in both the subform and main form.
I know how to create a command button that will delete the current record of which ever form I'm in (either the sub or main form) but I wish to do so both at the same time with one event procedure. I currently have been experimenting with a button in the subform but I don't really care if it's in the subform or main form.
I have a form to add, edit, and delete Records from a table. I am using the following VBA
Private Sub cmdEdit_Click() If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then With Me.frmlEmpDetailsSub.Form.Recordset Me.txtAddEditname = .Fields("Name") Me.cboRoster = .Fields("Roster") Me.cboPermFctn = .Fields("PermFctn") End With End If End Sub
Instead of referring to the Subform to load the data i would like to refer to a combobox:
cboSearchName
Its not an issue but This Combobox contains 5 columns...
I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.
What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.
Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.
INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE ) SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE] FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN] WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));
Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.
Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.
i have an MS Access db containing a few tables of 'calls recorded' and 'finished calls'.
(i want to move all 'Finished calls' to a seperate 'Finished Calls' table)
anyway, the thing i want to do is to move records from Table1 to Table2, i am able to do this using the following code...
SELECT * INTO Table2 FROM Table1 WHERE Finished='Yes';
this code does work, however it does not delete the records that were moved out of Table1 - they are still there, its more or less copying and pasting them..
Here's a tough one that has been driving me crazy! (Probably easy, but I don't want to admit it!)
I have a form with a listbox called "lst_exclist". The recordsource for this listbox is the following query:
SELECT tbl_collexcludereasons.priority, tbl_collexcludereasons.excname, IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') AS enabled, tbl_collexcludereasons.priority FROM tbl_collexcludereasons ORDER BY IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') DESC , tbl_collexcludereasons.priority;
Which basically gives me this with dummy data: 2 PIPELINE ENABLED 3 HELLO ENABLED 5 GOODBYE ENABLED 1 BAD DISABLED 4 GOOD DISABLED 6 LAST DISABLED
I had two command buttons, one up arrow and one down arrow. The up arrow is supposed to move the selected record on the listbox up by exchanging the next lesser priority number with itself. The down arrow does the same. The DISABLED records are supposed to be ignored on the move up and move down procedures, meaning only ENABLED records are allowed to exchange priority numbers, and thus move up or down on the list. I tried the following code:
Me!lst_exclisthidden = Me!lst_exclist
DoCmd.SetWarnings False
Dim startingnumber As Integer Dim endingnumber As Integer Dim nametochange As String Dim getchangerst As DAO.Recordset
If IsNull(Me!lst_exclist.Column(0)) = True Then MsgBox "Please choose an entry on the above list to move.", vbCritical, "Error" Exit Sub Else End If
If Me!lst_exclist.Column(2) = "Disabled" Then MsgBox "There is no need to move a disabled selection, please enable the selection to change it's priority.", vbCritical, "Error" Exit Sub Else End If
If Me!lst_exclisthidden = acLast Then MsgBox "You cannot move the bottom selection on the list down, please choose another one.", vbCritical, "Error" Exit Sub Else End If
But it seems to loop when it gets the the area it is checking for numbers because the highest records is disabled, so it's ignored. How can I make this work? Please help!
Hello. I have a question that seems simple but I can't get it to work. Any help would be appresiated.
I need a form that will pull the oldest record from one table, allow the user to add a few fields to the record, then paste the record to a different table. when it is done the original record has to be removed from the original table.
I have tried using a append Query, but it moves all of the records at once and doesn't allow data entry on each record. I'm not sure if this would be done by a Query, Form or Macro. I have laid out the nessacary data flow below incase there is an easier way to do this.
Persons A,B, and C enters data into table 1. later person D pulls the oldest record from table 1, reads the data then adds fields to the record and records it to table 2. The information is then removed from table 1. Person D then goes onto the next record from table 1. When person D is done, there will be no data left in table 1.
Any Ideas? or can someone point me in the right direction for help?
i have a form, where a user inputs a account number inputting the account number, then populates all the relevant table fields, into the form for the user to edit.however if there is more than one account, with that account number, how do i get it so they can cycle through on the form?
I have an order system whereby there is a "basket" table and an order detail table.
I want to use an append query to move all the records from the basket table to the order detail table.
However, I also need to mark each record that gets appended to the order detail table with an "Order Id" that has already been saved in an Order table.
I.e. I save the main details of an order into the order table then copy records from the basket table into the order detail table along with an order ID that comes from a form.
I'm having trouble with one of my reports. attached is a screencap of my report. The second name which is highlighted i need to either nudge it to the right so it is not directly under the first name OR remove it completely but keep the times and a gap for the next record.
I am creating is a sign in database where staff sign in/out for each day of a week. the issue is when the staff member signs in/out in the morning and then does the same when they get back from lunch it is difficult to read. This report is only printed once a week and will not show after a time frame of 5 days.
So, i still need that name to show up for the next day.
I'm using a form with next record and previous record buttons that works fine but as the size of the database has grown it's clear I need a faster way to move through records.
My solution was to have an advance 10 records at a time button but I'm having problems when reaching the far sides of the records.
For example: If I'm on record 9 and I press go back 10 button then access tries to go to record 0 (which doesn't exist) and brings up and error. The same happens if I try to advance more than total number of records.
I found a solution to this which was to disable the button when the recordcount was less than 10. This works except now if I'm on say record 14 and want to go back to 4, i press the button, i goes back to record 10, the button is disabled so it can't go back further and i get an error.
I've got a form with numerous controls and labels. When i first open the form, all looks fine - all labels are Black Arial 8 Bold. But when i start scrolling through the records, the text on three of my text labels gets bolder. I haven't a clue what is causing this - the labels are just standard - two are associated with controls and one is all on its own.
I have no code written to do this, and in fact i'm not sure you could write code to do this. I've tried renaming the labels, changing the font, deleting and recreating them, but it's always the same three that get darker.
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
Move Selected Items Between Listbox1 (listEmp) and listbox2 (listAllocated). the items moved from listEmp must be removed from listEmp when moved to list allocated and vice versa.
Overview:
listEmp is my first listbox and currently has the query in it's rowsource SELECT e.* FROM qryEmp AS e; it has 7 columns but only 6 are showing (Column 1 is hidden) and it is currently bound to 1
listAllocated has only 4 columns (can be changed) i only need the first 4 columns from the first list. currently it has no row source and the row source type is set to Value it is also bound to 1
I did find some code that does not do what I need but is closer than anything that I have found so far. It seems like it is copying the Selected Items from Column 1 and pasting it in the second listbox. but it is pasting them all in a row and not in individual columns below is the code. I will post a screen shot in 2 posts time.
Code: Private Sub cmdCopyItem_Click() CopySelected Me End Sub
I have a subform that I use to scan in products using a barcode reader, when the item scans it moves to the quantity (Qty) field where I have this code:
Code: Private Sub Qty_GotFocus() Qty.Value = 1 DoCmd.GoToRecord , , acNewRec txtOrderNo.SetFocus End Sub
I am simply trying to set the Qty value to 1 and move onto the next record ready to scan another product in.
And trantype look-up field containing transaction type debit or credit.
Here in this form i want when i select debit transaction type then control move to the Damount and on pressing Enter key Camount skip and control move on Description, and same like this when i choose transaction type Credit then Damount skip away and control move on Camount field and next to description, and access doesn't ask for enter value in Damount.
