Forms :: Move Records To New ID Or Delete?

Apr 26, 2014

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

View Replies


ADVERTISEMENT

Forms :: How To Move Records With Down Button In A Tabular Form

Jun 28, 2013

Is it possible to move down records with the down button in a tabular form to the same field in the next record.

View 11 Replies View Related

Forms :: How To Delete Transfer And Associated Tools Records

Sep 7, 2013

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?

View 2 Replies View Related

Forms :: Delete Button Deleting Too Many Records

Aug 8, 2013

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:

[Code] .....

View 3 Replies View Related

Forms :: Delete Records Based On Inputs

Nov 23, 2013

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 _

[Code]...

View 9 Replies View Related

Forms :: Check Boxes To Enter And Delete Records?

Jun 24, 2013

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.

View 14 Replies View Related

Modules & VBA :: Command Button To Delete Records In Sub And Main Forms

Jun 24, 2013

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.

View 2 Replies View Related

Forms :: Add / Edit And Delete Records From A Table Based On Combo

Dec 12, 2013

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...

View 2 Replies View Related

Forms :: Delete Records In Subform Based On Check Box In Parent Form

Mar 1, 2015

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.

View 5 Replies View Related

Move Records

May 6, 2005

is there a way by which i can move one or more records from one form to another when clicked on the 'YES/NO' button ??? :confused:

thanks in advance.

View 5 Replies View Related

Need To Move Data From Table A To Table B And Delete It

Sep 20, 2006

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.

View 4 Replies View Related

Move Records Between Tables

Nov 22, 2005

hiya, im new to this sql scene..!

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..

any help would be much appreciated!

View 14 Replies View Related

Move Records On ListBox

Mar 29, 2006

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

startingnumber = Me!lst_exclist.Column(0)
nametochange = Me!lst_exclist.Column(1)

endingnumber = startingnumber + 1

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

While DCount("*", "tbl_collexcludereasons", "Priority = " & endingnumber & " and Enabled = -1") = 0
endingnumber = endingnumber + 1
Wend

DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = tbl_collexcludereasons.priority - 1 " & _
"Where tbl_collexcludereasons.priority <= " & endingnumber & " and tbl_collexcludereasons.priority > " & startingnumber

DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = " & endingnumber & " " & _
"Where tbl_collexcludereasons.excname = '" & nametochange & "'"

Me.Refresh

Me!lst_exclist = endingnumber

Me!lst_exclisthidden = Null

DoCmd.SetWarnings True

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!

Thanks

Vassago

View 1 Replies View Related

Using A Form To Move Records From One Table To Another.

Jun 27, 2005

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?

View 3 Replies View Related

Modules & VBA :: Move Between Records On A Form

Aug 11, 2014

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?

View 2 Replies View Related

Using An APPEND Query To Move Records From One Table To Another...

Mar 10, 2008

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.

The order ID is an autonumber.


Thank you all so much in advance =]

View 3 Replies View Related

Reports :: Move Or Hide Duplicate Records?

May 8, 2013

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.

View 1 Replies View Related

Modules & VBA :: Move 10 Records At A Time Button

Nov 29, 2013

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.

View 3 Replies View Related

Weird - Font On Text Labels Gets Bolder As I Move Through Records

Oct 19, 2005

Hi all,
title says it all!

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.

Ideas?

P.

View 1 Replies View Related

Queries :: Drop Down List Selection - Move Records From One Table To Another?

Sep 15, 2013

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 ...

View 1 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

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

View 14 Replies View Related

Delete Duplicate Records Or Blank Records

Mar 19, 2007

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.

How can I do this?

Thanks,

Dave

View 3 Replies View Related

Forms :: Move Selected Between Two Listboxes?

Dec 16, 2013

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

[Code].....

View 7 Replies View Related

Forms :: Move A Form Without Borders?

Mar 20, 2014

move a form without borders but not for microsoft access it's for visual basic microsoft access 2013

View 3 Replies View Related

Forms :: On Got Focus Move To New Record

Jun 23, 2013

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.

View 10 Replies View Related

Forms :: Control Move On Next Field

Feb 7, 2014

I have a form containing these fields

trantype,Damount, Camount,Description

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.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved