How To Give A Commit Statement In VBA?

Mar 6, 2007

Hi,

Do i have to give a commit statement when i update tables in backend(insert, delete,update) from a form, say when user clicks submit, it takes the data in fields and inserts it into the table , but till now i never did that but i could see all data when i queried the table, but i am not sure if that would be right, if i have to give Commit, how do i do that using VBA?

Any help would be appreciated!

Thanks so much in advance!

View Replies


ADVERTISEMENT

Forcing A Record To Commit

Aug 26, 2004

This should be an easy one, but I’ve been know to say that before…

There are 2 tables (Experts and Education) I also have a form called Edit expert. On this form I have a tab control with 2 tabs. Each tab has a subform on it. The subform data entry property is set to yes. I want the user to enter the contact info of an expert on one tab, click the next tab and enter the education that the expert has. The 2 tables are in a one to many relationship with ExpertID being the primary key. So when you add a record to the education table you need the ExpertID. Well I want to be able to give it to the user. I have a control ExpertID on both subforms. On the first subform, the control source is ExpertID from the Experts table. On the second subform, the control source is set to the ExpertID on the previous subform. It works great until access tries to enter the record into education. Even though the correct expertID is in the box, it says that “you can not add or change a record because a related record is required in the table experts” so this leads me to think that access has not yet committed the Contact info record yet. What I want to know is can I force access to commit the record before the user moves onto the next tab? I have zipped a dumbed down version so you can hopefully understand what I’m rambling about =) all you need to do is pull up the Edit Expert form, enter a rec and then click on the Education tab. you'll see the correct ExpertID for the record you are about to enter, but after you enter the rec and try to click else where it yells at you. thanks for your time and effort!! - me

View 1 Replies View Related

Please Help - I Give Up!!!

Dec 21, 2006

I couldn't find a similar question already answered and I am stuck at home due to the blizzard here in Denver Colorado so I can't get any other resources to help. I am working on a select query where I am using two table's and another select query to pull information.

query HOLDINGS HASH QUERY #3:
- AS_OF_DATE
- SAP
- ACCNTNUM
- SumOfSumOfAS_OF_BOOK_VALUE_4TH

table HOLDINGS HASH CGAAP ADJUSTMENT TABLE:
- BEGIN DATE
- END DATE
- SAP
- ACCNTNUM
- SUM VALUE

table SAP DETAIL TABLE:
- Fiscal Year
- Company Code
- Account Number
- SumofAmt CoCd currency
- Cabinet

Currently the join properties include: from SAP DETAIL TABLE the Company Code and Account Number to the HOLDINGS HASH QUERY #3 SAP and ACCNTNUM, from HOLDINGS HASH QUERY #3 SAP and ACCNTNUM to the HOLDINGS HASH CGAAP ADJUSTMENT TABLE SAP and ACCNTNUM.

There are three things that I am trying to accomplish with this query. The first is pulling down the SAP and ACCNTNUM fields which works fine. The second is pulling down the SumOfAmt CoCd currency and SumOfSumOfAS_OF_BOOK_VALUE_4TH which matches the SAP and ACCNTNUM fields which works fine.

What I'm trying to do for the third is to only pull the SUM VALUE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE that matches the SAP and ACCNTNUM fields and also where the AS_OF_DATE from the HOLDINGS HASH QUERY #3 is between the BEGIN DATE and END DATE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE.

This query will then be the output for a report.

In order to accomplish the third task I have tried to complete a SELECT statement and a IIF statement that have not worked. I am sure the select statement would work but I just can't figure it out. Any help would be very very much appreciated.

Sam

View 2 Replies View Related

Trying To Give Each Occurance An ID

Sep 6, 2006

Hi Guys, Ive been struggling to create a query, I wonder if anyone knew how to do this.

I have data setup basically like this: -

Code Customer
A CustA
A CustB
B CustC
C CustD
D CustH
D CustI
E CustE
F CustF
G CustG

Is there a way i can make a count work to show like this: -

Code ID Customer
A 1 CustA
A 2 CustB
B 1 CustC
C 1 CustD
D 1 CustH
D 2 CustI
E 1 CustE
F 1 CustF
G 1 CustG

So if there is more than one customer, the ID will accumulate until the code changes so it will revert back to one again.

Hope someone knows how to do this.

Thanks

Predator002

View 2 Replies View Related

About Ready To Give Up On Access

May 12, 2005

I work at a Phamaceutical company in the US (based in London) and have a database that tracks atypical events. It has normally about 10 concurrent users and continually (once a week sometimes more sometimes less) corrupts. I have spent a lot of time validating the database writing the user requirements, functioanal and design specs as well as a 150 page IOPQ.

It is a split database with both front and back ends on a file server. I know this is not the best way to do it but the database is frequently updated with requests for new reports etc and I am not sure how to push down the front ends to all the use pcs. The database is about 40 mb.

I like the database. It is fully audit trailed and does what I need it to do, but the corruption thing is driving me batty. I have the jetutils with the viewer to see who is in the db (by pc name) and then I cross reference that to the person in the db and call them to get out. It is a real pain in the arse and I am just about ready to agree with the IT guys that Access is not a multi user database.

Should I scrap Access and go to SQL Server?

View 7 Replies View Related

Can I Add A Column To A Table And Give It A Name Using VBA?

Feb 22, 2008

Hi all

I am currently building a holiday planner for my employers and I would like to try and display the days of the year as rows and the staff as columns (Fields). When someone adds a new member of staff, I would like it to also add them to the calendar table as a new column and name the column the same as the individuals name. Is this possible using VBA or SQL string?

If anyone has a better idea as to how I can display the forthcoming holidays for a team I would really appreciate your comments.

Thanks

Gareth

View 3 Replies View Related

Copy A Query And Give New Name

Mar 11, 2015

I have Access 2010 and I have a query that I want to take a copy of, save as and call it call it something else in the same database.

For example "query1" I want to save as and call it "query2" .

View 7 Replies View Related

Diff Expresions Should Give Same Result But Don't!

Sep 22, 2006

I've got a table in which there are 20 numeric columns. For one report, I want to add all of the data in those columns together. I used the expression:

Seats: Sum(DailyActivity!PkgSeq01Cnt+DailyActivity!PkgSeq 02Cnt+DailyActivity!PkgSeq03Cnt+DailyActivity!PkgS eq04Cnt+DailyActivity!PkgSeq05Cnt+DailyActivity!Pk gSeq06Cnt+DailyActivity!PkgSeq07Cnt+DailyActivity! PkgSeq08Cnt+DailyActivity!PkgSeq09Cnt+DailyActivit y!PkgSeq10Cnt+DailyActivity!PkgSeq11Cnt+DailyActiv ity!PkgSeq12Cnt+DailyActivity!PkgSeq13Cnt+DailyAct ivity!PkgSeq14Cnt+DailyActivity!PkgSeq15Cnt+DailyA ctivity!PkgSeq16Cnt+DailyActivity!PkgSeq17Cnt+Dail yActivity!PkgSeq18Cnt+DailyActivity!PkgSeq19Cnt+Da ilyActivity!PkgSeq20Cnt)

Where dailyactivity is the name of the table, and pkgseq01cnt is the name of the first column, etc.

The expression returns the number 961.

If i make a query where i sum all of the columns individually, and then a query based off of that query where i add all of the sumed columns together, i get the number 965. (which, as far as i can tell, is the right number)
the expression in the second of those querries is:

Seats: Sum([seat]![SumOfPkgSeq01Cnt]+[seat]![SumOfPkgSeq02Cnt]+[seat]![SumOfPkgSeq03Cnt]+[seat]![SumOfPkgSeq04Cnt]+[seat]![SumOfPkgSeq05Cnt]+[seat]![SumOfPkgSeq06Cnt]+[seat]![SumOfPkgSeq07Cnt]+[seat]![SumOfPkgSeq08Cnt]+[seat]![SumOfPkgSeq09Cnt]+[seat]![SumOfPkgSeq10Cnt]+[seat]![SumOfPkgSeq11Cnt]+[seat]![SumOfPkgSeq12Cnt]+[seat]![SumOfPkgSeq13Cnt]+[seat]![SumOfPkgSeq14Cnt]+[seat]![SumOfPkgSeq15Cnt]+[seat]![SumOfPkgSeq16Cnt]+[seat]![SumOfPkgSeq17Cnt]+[seat]![SumOfPkgSeq18Cnt]+[seat]![SumOfPkgSeq19Cnt]+[seat]![SumOfPkgSeq20Cnt])

where 'seat' is the name of the first query that sums the columns.

I think those two ways should both come up with the same number - but i can't figure out why the first way is missing four! there are thousands of rows so it is hard to go through the table itself. Also, in both of those ways, the querry includes a 'where' column which limits it to rows where another column equals a certain word - but that's relatively straight forward - dont' know why that would make it different.

any ideas?

Thanks! It's driving me crazy!

View 2 Replies View Related

Making Query Give Me Anything For Dates BETWEEN 10/01 And 10/15

Nov 10, 2006

Hi!

I'm trying to write a query that will display my data for anything that has a date between 10/01 and 10/15. I went into the "Build" area and looked at the "functions" available but none of them sound right - and the one book I have on Access is not so great.

Can someone help me with the correct formula?

Thanks!

View 2 Replies View Related

Can Someone Give Me Some High Level 'pointers' ?

Oct 25, 2006

Can someone give me some high level 'pointers' ?

Simply (although not for me) I am trying to build a form that involves a unique identifying number being entered, a button being pressed and relevant field belonging to that unique identifying number being displayed in a subform.

For example, I might want to enter a customer number, press a button and all the orders made by that customer appear in a subform.

Any help gratefully received !

Thanks.

View 3 Replies View Related

Subdatasheet Not Sufficient, Please Give Alternative

Dec 28, 2004

What I am doing, is I have various products with 5 basic fields. I then wanted to add an array of text fields to 2 of those original 5 basic fields. I attempted to do this with a subdatasheet, however, a subdatasheet is not record specific, is not directed to a specific field, and really does not do anything that I want.

Say that the basic field is ProductDescription. I want one column of fields to correlate to it, like below.
ProductDescription
Is blue
Is large
Is heavy
Is strong

Each product will have a different amount of varying descriptor fields. Once understand how to incorporate this with my project, I then will need to understand how to add the subdatasheet to a report easily. Thanks for any help,
-SethTherrien

View 3 Replies View Related

General :: No More Records Available To Give Message

Dec 11, 2013

I have a table that is a list of available UPC codes that through forms are being assigned to several different tables (types of items). When the UPC code is assigned, there is a yes/no box which is checked. I need to have a message box appear when the last of these UPC codes has been used (or better yet perhaps when there are a specific number left - such as 10 - so the user isn't all of a sudden locked out of any more work).

It could even be triggered when a specific UPC code is reached - they will be in numerical order (ignoring the check digit). Is there anyway to do this globally or would the check need to be on each form as the UPC is assigned?

View 6 Replies View Related

Adding 2 Fields To Give The Default Of 3rd Field

Jun 15, 2006

Hi people,

May I know how to add 2 fields.

i.e
My table has 3 columns

Field1 Field2 Field3

The value of field3 = field2 + field1 (the formate of field1, field2 n field3 are currency)

how do I indicate that the value of field3 is depended on the value of field2 n field1

is it under the default value of field3(in the TABLE design view)that i indicate field2 + field1?

thank u

View 1 Replies View Related

Please Give Me Feedback On My Table Structure, Will It Work?

May 2, 2007

Hello dear experts!
Will this table structure work? - Please help!
I need to be able to see if certain members have paid subscription, see if stakeholders have paid for their room bookings, etc. Will it work, does it look ok to you?

Im trying to brush up my normalisation skills and logic.

Please click the link below to see my ER-D:

17320

Thanks, Very much, Ant...

View 8 Replies View Related

Counting With A Query To Give Results On Report

Dec 18, 2007

I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.

Can someone help me with this?

- Thanks in advance!

View 10 Replies View Related

Query On Form. (combo Box To Give Criterea)

Mar 22, 2006

I have changed my approach for my database, and i think a query is what i needed all along. But i am not sure how to go about it.

Is it possible to have query results on a form?

more precise, i have a table full products, alot of information per product (about 30-50 fields)

The amount of actual products isn't many probably around 10, and it's likely to stay at around 10 products.

Can i have a drop down list listing all the products, so it looks these up from the product table, you select the product, that product field value becomes the criterea for the query, and all the associated form fields get filled out to do with the product selected.

To summarise, can i have this all done in a form? so it's easy for staff to simple go the to product drop down list, select a product, all the info relating to the product is then displayed on the form.

hopefully i have made sense with my post.

View 10 Replies View Related

Forms :: Non Matching Records To Give Message Box?

Dec 11, 2013

I have two tables, categories and items. I have a form that is linked to the categories table and a subform that is linked to the items table. The user selects a category on the main form then an item to add to the category on the subform. (all of this is functioning correctly - but you need to understand for my question.)

Both the form and the subform have a text box that displays a record from their respective table, for the example we will call this color.

What I need to do is create code that checks the color text box from the category (main form) against the text box for the item (subform) and if they are not a match I need to display a message box - from where I can then add the necessary actions.

EDIT: note this is not a query on an entire table or 2 it is just comparing the two text boxes on the form and subform

View 5 Replies View Related

Forms :: Can't Give Conditions On Validation Rule?

Jun 1, 2015

Can't we give conditions on Validation Rules? (Like IIf)

IIf(getusername()=[Log_Resource],Between DateAdd("d",-1,Date()) And Date(),Between DateAdd("d",-2,Date()) And Date())

View 8 Replies View Related

Reports :: Give Control A Unique Criteria

Apr 15, 2015

I have a report that is filtered like Year_ = "2014/15".

Say for example on this report it contains two text boxes:

Text box 1 has a control source of "March_T"
Text box 2 has a control source of "Aprril_T"

How can I make text box 1 be filtered by Year_ = "2014/15" and text box 2 be filtered by Year_ = "2015/16"?

View 2 Replies View Related

Forms :: How To Give User Authority On One Button

Apr 27, 2014

how to give someone authority on one button in form

View 8 Replies View Related

How To Link Table And Give Reference Of Column

Sep 10, 2015

I have created two tables, I want to copy one column from one table to another table column with repeat value in rows.

example :- copy "Material number" column from Table1 to Table 2. In Table 2 Material number will repeat in 20 rows.
Like in excel we give one cell reference to multiple rows.

How I can link table and give reference of column.

View 1 Replies View Related

How To Give Primary Key Increment And Decrement Automatically

Aug 8, 2015

I want make one database about office register. I gave primary key for auto increment serial number . That is working fine. Now problem is some times I want delete client name in between table .That is maybe first row or 5th row or anywhere . at the time the row deleted which is i was selected but the primary key is not update . for example I want delete 8th row . after deleting I not seen 8 in primary key place i have seen only ....,5,6,7,9,10...... etc. how to give auto increment and decrement in the primary key......

View 3 Replies View Related

SEARCH Button To Give Results On Listbox

May 7, 2013

How to create a search BUTTON to give results on the listbox after a user typing the desired keywords to search.

As of now, the database has "On Change" property that whenever a key is pressed (from time to time; letter per letter) it automatically change. What I want is for the user to finish the word he/she wanted to search then theres a Search BUTTON to press in order to show the results.

Attached is the database...

And also, how to put Reset BUTTON - to reset the search box and ready for the user to type again.

View 7 Replies View Related

Taking One Date From Another To Give Number Of Days Left

Dec 11, 2006

HI, this should be relatively simple, but for some reason I cannot figure it out.

I have a field titled "DEADLINE" - a user inputs a date in this field (the datatype for this filed id date/time)

What I want to do is, on the form create a text box which takes the deadline date - todays date to give me the number of days to the deadline date. Once the dealine date has passed I then want to turn the counter to Red to show it is overdue. If there is no dealine then I want the field to say something like "No Deadline Set"

View 5 Replies View Related

How To Make Validation Text Give Different Error Messages

Apr 13, 2006

In of my tables called Employee Payroll, I have several earnings fields, and in a previous post I mentioned that I have implemented a validation rule for them which is £2.50 or £2.75 or £3.10. I have also used an error message in Validation Text to help the user if they enter an invalid value.
How can I change this message to reflect what the user types in? For example, if they enter an erroneous value like £9.34, the error message would say, "You have entered an erroneous value" or if they type an extreme value like £3.40 or £0.01, the error message would say, "You have entered an extreme value".
I understand that I would need to state when the data becomes erroneous and when it becomes extreme- so I thought, anything above, £4.00 should be erroneous and any negative values too. Extreme data would be anything between £0.00 and £3.10.
Would I need to use some kind of code in VBA?
Thanks

View 2 Replies View Related

Queries :: Give All Null Values 0 In Calculated Fields?

Jun 12, 2013

I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.

What I have tried:

1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.

2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.

3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.

The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.

View 14 Replies View Related







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