Copying Records Based Off Of Conditions

Jul 8, 2006

Hello. Can this be done?

I am creating a mini scheduling program, and I have one table that handles all requests (tblRequests), then a second table is the final schedule (tblFinalSchedule). On the table for the requests, I have a field called "locked" that is of type yes/no. The purpose of this field is for a meeting time that is of uptmost importance and that I don't want the program to manipulate it in any way.

Is there a way to program in VBA so that when a button on a separate form is clicked, those records that when the yes/no field is checked (set to true), are copied into the appropriate fields from tblRequests into tblFinalSchedule?

View Replies


ADVERTISEMENT

Query Based On Multiple Conditions

Jan 11, 2005

For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked

Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...

I can think it through logically but cannot get my head around the query.

If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless

If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;

Then i also need to do the reverse for the Date Loan Booked, i.e.

If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;

Phew! That probably makes no sense but i had to try.

Once again, i appreciate any help so that i can keep the remainder of my hair.

Switters

View 1 Replies View Related

Modules & VBA :: Check If Record Already Exists In Recordset Based On 2 Conditions

May 27, 2014

I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.

I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.

Code:

dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value

[code]...

View 9 Replies View Related

Counting Records With Multi IIF Conditions

Jun 8, 2014

I want to count the records with multi iif conditions. I am able to get one condition right but don't know how to put more than 1.

Eg: =Count(IIf([Step #]=1 And [Type of Work]=1,0))

But I am not able to get if I want step#1, step#2 and step#3 all together with type of work = 1

View 5 Replies View Related

Modules & VBA :: Prevent Deletion Or Changes To Records That Meet Certain Conditions

Jun 14, 2013

Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.

The problem I am having is the code is contradicting each other preventing some of the records to be locked down.

Code:
Private Sub Form_Current()
'Code 1 - Prevent edits if 21 day period has lapsed
If TestDate2(Me.txtStartedHidden) > 0 Then
MsgBox "Editing NOT allowed - 21 day time period has been reached"
Me.txtBasic.Locked = True

[Code] ....

Rules are:

Edits allowed if invoice date not reached
Edits allowed if within 21 day but NOT if invoice date is reached

Edits prevented if invoiced date reached and if records still within 21 days
Edits prevented if invoiced date reached & records past 21 days

View 2 Replies View Related

Copying Multiple Records

Nov 6, 2006

Hi all!

I need a little help with a new function in our CRM db (Ac2000).

I've made a system for handling sale-prospects. When a sale is done, I want to transfer the costumer info to the costumer table. All this is well, no problems with this.

BUT, each prospect (And costumer) has contacts. There can be several contacts to each prospect, connected with a prospect number.

The challange is to transfer the contacts as well. And assigning the new costumernumber..

Dont bother going techincal right away, but a point in the right direction would be great!

View 7 Replies View Related

Copying Records To A New Table

Feb 16, 2005

I am trying to help a new employee with an MS Access 2000 problem. The db was created years ago, and the main table (permits) needed some major changes. She created a new db with a table that has all the same fields as the old db, but many new ones as well. We want to now import the data from the old db's "Permit" table to the new db's "Permit" table. All the field names are correct, but we keep getting errors trying to import and export. There are approximately 940 records.

Is there an easy way to do this? Thank you!

Mike

View 1 Replies View Related

Queries :: Copying Multiple Records From One Table To Another

Sep 7, 2013

I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:

WorkOrderPartID (Autonumber)
WorkorderID(Number)
PartID(Number)
Quantity(Number)
UnitPrice(Currency)
Notes(Memo)
KitID(Number).

[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":

DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"

My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...

View 14 Replies View Related

Forms :: Copying Multiple Records From One Table To Another And Other Stuff

Sep 5, 2013

It basically is used to create, manage and track Customers, Parts, Work Orders and Suppliers, payments and employees.You simply create a Customer and then create a Work order that contains a list of parts and labour items.

What I want to do is to create a "Kit" of parts so that the user does not have to select each part individually when they create a Work Order. For example, if I use a mechanics workshop as an example, the mechanic may sell his "Signature 1916cc Engine" that consists of 40 different parts and some other items such as labour and other services. In this scenario, instead of the user individually selecting 40 different parts, all he needs to do is select the Kit called "Signature 1916 cc Engine" from a drop down list and all of the 40 parts and other items would automatically populate the correct Work Order Parts/Labour tables.

I would also like the user to be able to select another "kit" that would either replace all parts and labour records or append to what is already there.

I have changed the form called "Workorders" to have 2 buttons. One called "Load Kit" the other called "Save Kit".If the user presses "load Kit" a form will be displayed that enables them to select a "kit" from a list and whether they want to replace all of the existing parts and labour records or if they want to append to the existing records.

In order to create a new kit, the user can simply manually create a new work order by individually selecting each part and labour item and then pressing the "Save Kit" button. The user will then be asked to provide the short name of the Kit and the long description (I have created table called tKit). The new kit will then be saved.

It appears that the "Entered by" field that is linked to the Employee table must not be blank other wise the Workbook is NOT saved....I have tried to figure out why this is happening but can't!

The Work Order Parts are stored in "Work Order Parts" and the Work Order Labor is stored in a table called "Work Order Labor". I have created 2 extra tables called "tKitWorkOrderParts" and "tKitWorkOrderLabor" that I assume will contain the items that make up the kits..

View 14 Replies View Related

Forms :: Copying Data To A New Record - Skip Null Records

Aug 13, 2013

I've created some code that enables a user to copy an existing record to a new record, which looks like this:

Private Sub btnCopytoNewRecord_Click()
Dim Salutation As String
Dim First_Name As String
Dim Surname As String

'Copy fields to variables
Salutation = Me.Salutation

[Code] ....

This code is working fine, until it hits an empty field, and then I get an error message: Run time error '94':

Invalid use of NullIs there some code I can use to tell it to skip any null fields?

View 2 Replies View Related

Modules & VBA :: Copying File To Specified Folder And Updating Records With New Location?

Jan 28, 2015

I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.

I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.

I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.

Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.

Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?

Here's the VBA I'm using so far...

Code:
Option Compare Database
Public Sub GoCopyThumbnail()
Dim fDialog As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
Dim LUser As String

[Code] ....

View 2 Replies View Related

Modules & VBA :: Copying Records From Linked Table / Each Record Slower And Slower

Jul 30, 2015

I have an access db with a linked table (to Sharepoint) which contains about 15000 records. I have to copy those records to the local access db. To do this I have a bit of VBAcode. It works, but for each next record that is copied it takes a bit longer. When starting the program the records are copied quite fast, but soon you see it slowing down until after a couple of thousand records it just goes too slow to be usefull, meaning it would take a lot of days to copy all records.

What I now do is let it copy 500 records and exit ms-access completely and then start it again and let it copy the next 500 records. This works but is tedious as I have to do it about 35 times. Why this gradual slowdown occurs?

The VBA code is pretty straightforward.(I use VBA because I do a bit of data wrangling while reading in the values of the records)

Code:

Dim importdb1 As DAO.Database
Dim rsimportdb1 As DAO.Recordset
Dim i As Double
Set importdb1 = CurrentDb
Set rsimportdb1 = importdb1.OpenRecordset("2015")
Set rsMain = CurrentDb.OpenRecordset("tbl_import")

[code]....

View 3 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

Copying A Set Of Records Across Several Tables To Same Tables With A Few Changes

Apr 8, 2015

There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.

There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked?

View 4 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

View 14 Replies View Related

If 2 Conditions Are Met Then

Feb 26, 2007

I am trying to write a code that will enable an event to happen if 2 conditions are true, something like:

If [DueDate] >= [CurrentDate] And [EndDate] is Null Then
Event ..... take place

I tried above and did not work

it works only if write one condition of above

how can I set it up correctly

View 5 Replies View Related

Help With Conditions

May 26, 2005

I currently have a query which takes its criteria from a form called search. This all works fine, unless the user leaves a field in the search form blank. Then the query has no results.
What I'd like is for a blank field to act as a wildcard.
I guess the reason this doesn't automatically work is that Access (effectively) puts " & " around the criteria taken from a form, so the criteria ends up as " " not (there's a space there!).

I tried entering a criteria in the query that says:
IIF([Forms]![Search]![Date] Is Null,*, [Forms]![Search]![Date]
this doesn't work. For one thing Access edits the * into "*", so it is no longer a wildcard. Secondly, I've never used IIF before, so it's probably wrong anyway...

So, can anyone help me do this?
Even if a blank field cannot be used as a wildcard, any character would do...

View 2 Replies View Related

IIF With 2 Conditions?

Aug 15, 2007

Hi

Hope someone can help with this. Had a search through the forums but nothing quite the same (although I'm guessing it's a simple one to solve.)

I need to calculate a figure based on 2 criteria. The permutations are as follows

If condition A is less than 20 and condition B is No then return 9
If condition A is less than 20 and condition B is No then return 12
If condition A is greater than 20 and condition B is No then return 13
If condition A is greater than 20 and condition B is No then return 15

Any suggestions?

View 3 Replies View Related

Conditions

Jun 7, 2005

Hi, I have created a Yes/No attribute. Im just wondering if a text boxes visibility can be dependent on the result of this. E.g. If a row is set to yes then the text box will be shown and if it is set to no the text box is made invisible. Thanks for any help

View 7 Replies View Related

Participation Conditions - How To ?

Dec 21, 2007

Hi again :)
Since I am new to Access I am having a problem figuring out how to set the participation conditions (optional or mandatory) for my relationships. I have looked through MS access online and forums, google etc. and found nothing.
I would be very grateful for any help :)
Kind Regards
Marie

View 1 Replies View Related

Sql Update With 2 Where Conditions

Jan 12, 2005

The following SQL statement with 1 where condition works fine, but the Next SQL statement with 2 where conditions does not work, a error message says Run-Time error ‘13’ type mismatch , what is incorrect in the statement when the AND MinID = 15 is added. The table data type in set as number



SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID & ";"



SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID And MinID = 15 & ";"



Phil

View 3 Replies View Related

Setting Conditions

Jun 3, 2005

I have set up a querry that gathers information from different tables:

ID | Name | StartDate | EndDate | NbOfDays |

Then, i am using a form to fill up this querry. Normal stuff.

1- How and where can i set a condition so the input of EndDate could not be smaller than StartDate?
2- How and where can i set up and write the formula that calculates the NbOfDays? (meaning number of days worked form start date to end date)

Thank you for helping!

View 5 Replies View Related

Multiple Conditions For Form

Apr 11, 2006

G'Day:

I have a form that I created that allows the user to input an "ExhibitNo" and then a "PlacingNo" to allow each entry to receive a set monetary amount (based on values in a table).

The "ExhibitNo" has two references linked to it. They are "ClassNo" and "ExhibitorID".

My supervisors want me to make it so that when the "ExhibitNo" is entered on the form that it will not allow a monetary amount.

This is where I started, but it does not work. Does anyone have a better idea?

Private Sub Combo14_AfterUpdate()
Me.Refresh
If [ClassNo] >= 0 >= 5000 Then
If [ExhibitorNo] <= 0 >= 499 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If

If [ClassNo] >= 5000 Then
If [ExhibitorNo] <= 500 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If

If [ClassNo] >= 5000 Then
If [ExhibitorNo] >= 500 Then
[Premium] = 0
End If
End If

End Sub

Thanks

View 1 Replies View Related

Hide Form When All Conditions Are Met

Jan 23, 2007

What I need to do is, not have a form pop up when all the conditions are met. I have 30 some users that log into the db.

I have a form that pops up when the date of the project is past due. to certain users when they log in. I am using the Environ("username") theory, which works very well. But when the user has COMPLETED all the necessary dates, the form still comes up, (but of course it’s blank).

How can I eliminate that problem?

View 5 Replies View Related

Open Form On All Conditions Met

Feb 9, 2008

Hi,

i am trying to open a form if all conditions are met. if the conditions are not met, then they get a flash message and then the user should return to the open form. i have the condition checking working but cannot seem to open the form if ALL conditions are true. i also need to close the form when the new form opens.how could i achieve this? here is my code

Private Sub MidVerifyContinue_Click()

FullNameChk.SetFocus

If FullNameChk = False Then
MsgBox "You Need to have a Full Name to Verify", vbOKOnly, "Message"
End If

If CompNameChk = False Then
MsgBox "You Need to have a Company Name to Verify", vbOKOnly, "Message"
End If

If TradeNameChk = False Then
MsgBox "You Need to have a Trading as name to Verify", vbOKOnly, "Message"
End If

If UTChk = False Then
MsgBox "You Need to have a number to Verify", vbOKOnly, "Message"
End If

If CRChk = False Then
MsgBox "You Need to have a C R number to Verify", vbOKOnly, "Message"
End If

If NIChk = False Then
MsgBox "You Need to have a N I number to Verify", vbOKOnly, "Message"
End If

If PostCodeChk = False Then
MsgBox "You Need to have a Postcode to Verify", vbOKOnly, "Message"
End If

Exit Sub


DoCmd.OpenForm "New Verification Qry"

End Sub


many thanks,

Nigel

View 8 Replies View Related

Select Query With Conditions

Aug 9, 2005

Hi friends,

I had two tables parent(f1) , child(f1,f2,f3).
I had to display f3 value for parent(f1) when f1 has only one f2,
if f2 values are more than one for single f1 and if all f3 values are 0 then
i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then
is should display f3 value as '-'.

How to do this?
please any suggestions?

Thanks.

View 1 Replies View Related







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