Committing Records To Table

Im helping create an Access database to monitor IT stock.  The person who 
will be using the database wants a button on the form 'Stock_Level_Edit' to 
commit new records to a table 'Equipment_Table'.  I know this is not best 
practice, but this is what the person is looking for.  How do I do this?  
Just now the data is entered automatically without having to press a button.  
0
Utf
8/21/2007 4:02:03 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
807 Views

Similar Articles

[PageSpeed] 48

That's the default behaviour of Access, and getting around it is a nuisance.

You could convert to an unbound form, but that's a lot of work, and does 
create limitations on what you can do on a form.

You could create a module-level boolean variable (call it mbooOkayToSave).

In the Click event of the button, set the value of that variable to True, 
and save the data:

Private Sub NameOfButton_Click()

  If Me.Dirty Then
    mbooOkayToSave = True
    Me.Dirty = False
  End If

End Sub

In the form's BeforeUpdate event, check the value of the variable, and 
cancel if it's not True:

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Cancel = (mbooOkayToSave = False)

End Sub

In the form's AfterUpdate event, reset the variable:

Private Sub Form_AfterUpdate()

  mbooOkayToSave = False

End Sub

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message 
news:B59BF42F-A1F2-4A90-8B32-B48305BFF6A3@microsoft.com...
> Im helping create an Access database to monitor IT stock.  The person who
> will be using the database wants a button on the form 'Stock_Level_Edit' 
> to
> commit new records to a table 'Equipment_Table'.  I know this is not best
> practice, but this is what the person is looking for.  How do I do this?
> Just now the data is entered automatically without having to press a 
> button. 


0
Douglas
8/21/2007 4:14:25 PM
Ive put in the code you gave me, when i go to save the record i get an error 
message coming up:

run time error '2101'
the setting you entered isnt valid for this property

when i click on debug it takes me to the line:

Me.Dirty = False

Is the code you gave me to change the variable value?  If not then im not 
sure how to do this.

Thanks for the help

"Douglas J. Steele" wrote:

> That's the default behaviour of Access, and getting around it is a nuisance.
> 
> You could convert to an unbound form, but that's a lot of work, and does 
> create limitations on what you can do on a form.
> 
> You could create a module-level boolean variable (call it mbooOkayToSave).
> 
> In the Click event of the button, set the value of that variable to True, 
> and save the data:
> 
> Private Sub NameOfButton_Click()
> 
>   If Me.Dirty Then
>     mbooOkayToSave = True
>     Me.Dirty = False
>   End If
> 
> End Sub
> 
> In the form's BeforeUpdate event, check the value of the variable, and 
> cancel if it's not True:
> 
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> 
>   Cancel = (mbooOkayToSave = False)
> 
> End Sub
> 
> In the form's AfterUpdate event, reset the variable:
> 
> Private Sub Form_AfterUpdate()
> 
>   mbooOkayToSave = False
> 
> End Sub
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message 
> news:B59BF42F-A1F2-4A90-8B32-B48305BFF6A3@microsoft.com...
> > Im helping create an Access database to monitor IT stock.  The person who
> > will be using the database wants a button on the form 'Stock_Level_Edit' 
> > to
> > commit new records to a table 'Equipment_Table'.  I know this is not best
> > practice, but this is what the person is looking for.  How do I do this?
> > Just now the data is entered automatically without having to press a 
> > button. 
> 
> 
> 
0
Utf
8/22/2007 8:32:02 AM
I don't know what to say. That's valid code!

I did warn you that getting around Access's normal behaviour is a nuisance! 
Can you not convince the user that he/she should accept how Access works?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message 
news:E3C7B933-7134-4FBC-ABE7-ADC197B57232@microsoft.com...
> Ive put in the code you gave me, when i go to save the record i get an 
> error
> message coming up:
>
> run time error '2101'
> the setting you entered isnt valid for this property
>
> when i click on debug it takes me to the line:
>
> Me.Dirty = False
>
> Is the code you gave me to change the variable value?  If not then im not
> sure how to do this.
>
> Thanks for the help
>
> "Douglas J. Steele" wrote:
>
>> That's the default behaviour of Access, and getting around it is a 
>> nuisance.
>>
>> You could convert to an unbound form, but that's a lot of work, and does
>> create limitations on what you can do on a form.
>>
>> You could create a module-level boolean variable (call it 
>> mbooOkayToSave).
>>
>> In the Click event of the button, set the value of that variable to True,
>> and save the data:
>>
>> Private Sub NameOfButton_Click()
>>
>>   If Me.Dirty Then
>>     mbooOkayToSave = True
>>     Me.Dirty = False
>>   End If
>>
>> End Sub
>>
>> In the form's BeforeUpdate event, check the value of the variable, and
>> cancel if it's not True:
>>
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>
>>   Cancel = (mbooOkayToSave = False)
>>
>> End Sub
>>
>> In the form's AfterUpdate event, reset the variable:
>>
>> Private Sub Form_AfterUpdate()
>>
>>   mbooOkayToSave = False
>>
>> End Sub
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message
>> news:B59BF42F-A1F2-4A90-8B32-B48305BFF6A3@microsoft.com...
>> > Im helping create an Access database to monitor IT stock.  The person 
>> > who
>> > will be using the database wants a button on the form 
>> > 'Stock_Level_Edit'
>> > to
>> > commit new records to a table 'Equipment_Table'.  I know this is not 
>> > best
>> > practice, but this is what the person is looking for.  How do I do 
>> > this?
>> > Just now the data is entered automatically without having to press a
>> > button.
>>
>>
>> 


0
Douglas
8/22/2007 10:30:11 AM
Thanks for the help, were going to go with automatic save now.

Cheers

Tommy

"Douglas J. Steele" wrote:

> I don't know what to say. That's valid code!
> 
> I did warn you that getting around Access's normal behaviour is a nuisance! 
> Can you not convince the user that he/she should accept how Access works?
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message 
> news:E3C7B933-7134-4FBC-ABE7-ADC197B57232@microsoft.com...
> > Ive put in the code you gave me, when i go to save the record i get an 
> > error
> > message coming up:
> >
> > run time error '2101'
> > the setting you entered isnt valid for this property
> >
> > when i click on debug it takes me to the line:
> >
> > Me.Dirty = False
> >
> > Is the code you gave me to change the variable value?  If not then im not
> > sure how to do this.
> >
> > Thanks for the help
> >
> > "Douglas J. Steele" wrote:
> >
> >> That's the default behaviour of Access, and getting around it is a 
> >> nuisance.
> >>
> >> You could convert to an unbound form, but that's a lot of work, and does
> >> create limitations on what you can do on a form.
> >>
> >> You could create a module-level boolean variable (call it 
> >> mbooOkayToSave).
> >>
> >> In the Click event of the button, set the value of that variable to True,
> >> and save the data:
> >>
> >> Private Sub NameOfButton_Click()
> >>
> >>   If Me.Dirty Then
> >>     mbooOkayToSave = True
> >>     Me.Dirty = False
> >>   End If
> >>
> >> End Sub
> >>
> >> In the form's BeforeUpdate event, check the value of the variable, and
> >> cancel if it's not True:
> >>
> >> Private Sub Form_BeforeUpdate(Cancel As Integer)
> >>
> >>   Cancel = (mbooOkayToSave = False)
> >>
> >> End Sub
> >>
> >> In the form's AfterUpdate event, reset the variable:
> >>
> >> Private Sub Form_AfterUpdate()
> >>
> >>   mbooOkayToSave = False
> >>
> >> End Sub
> >>
> >> -- 
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Tommy2326" <Tommy2326@discussions.microsoft.com> wrote in message
> >> news:B59BF42F-A1F2-4A90-8B32-B48305BFF6A3@microsoft.com...
> >> > Im helping create an Access database to monitor IT stock.  The person 
> >> > who
> >> > will be using the database wants a button on the form 
> >> > 'Stock_Level_Edit'
> >> > to
> >> > commit new records to a table 'Equipment_Table'.  I know this is not 
> >> > best
> >> > practice, but this is what the person is looking for.  How do I do 
> >> > this?
> >> > Just now the data is entered automatically without having to press a
> >> > button.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
8/22/2007 1:02:02 PM
Reply:

Similar Artilces:

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

Excel for Customer History Records
Hello, can anyone direct me to an existing template that can be used to track customer enquiries and feedback. It needs an efficient way of recording follow up & response conversations. In a standard workbook layout if you type loads of text into a column (say 'follow up' for instance), it pushes all the rows so far down the spreadsheet it quickly becomes unwieldy. If anyone can help I could e mail an example of what we're using now (awful) and after having a chuckle at our expense maybe you can suggest something more appropriate. We're running 2003. Many thanks --...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

HTML Table import
Hi, I have a HTML document which contains a large data of data. When this html file is imported into excel some of the columns get out of whack. eg. Some columns are removed completely. This html renders ok in IE but get corrupted or misread when imported into excel, mainly when the table gets big. eg. It generally happens after the 10000 row mark. Any suggestions? Do you import it through data>import external data>new web query? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Ryan Hafey" <Ryan Hafey@discussion...

copying column width in tables
Hi, I created a table in Publisher with 6 columns. I would columns #1, 2, 4 and 5 to have the same width and columns 3 and 6 to have a different width. I'd like to set the width of column 1 and then paste that size to the other four so they all have exactly the same width. Presently, I am eyeballing it, but it takes too much time and is not exact. Any ideas on how to do this? The only way is to create guides. Arrange, ruler guides. You could try creating a table in Word, copy/paste. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsof...

look up tables and IF statements
=VLOOKUP(G2,$C$30:$F$369,2) I have managed to create a look up table using the office assistant, and the formula above dumps information into cell G3. If G2 is empty or has a zero I don't want G3 to have anything in it. At the moment it just keeps coming up with #N/A Can anyone tell me what formula I should be using please Hi Rick! Would this modification do? =IF(OR(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"") Hope this helps! -- Thanks and kind regards "Rick" wrote: > =VLOOKUP(G2,$C$30:$F$369,2) > I have managed to create a lo...

Pivot Table from two worksheets
I am trying to create a pivot table from two worksheets. Each workshee represents different accounts from which I am tracking expenses. I hav these columns on both sheets: Type of Expense (labeled A, B, C, D) Date, Description, and Amount. I want the pivot table to organize all of the data from both sheet according to Type of Expense and show the Date, Description, and Amoun under each Type of Expense. I can get it to organize it by Type o Expense and show a total for each, but how do I get it to show thi additional information? Thanks -- Message posted from http://www.ExcelForum.com ...

Subforms won't refresh after new record 11-17-09
Access 2003, XP Pro I have a main form with two separate subforms. I view various records by clicking on some list boxes. To add a new record, I select Records, Data Entry. I then (1) Make a selection in a combo on the main form. (2) Make a selection in a combo and enter some text in a textbox on subform1 (3) Make a selection in a combo on subform2 Subform2 has some code on the After Update event that saves the new record and goes to a previously selected record in cbo1 on the main form (cbo2 has code on the After Update event to go to that record). Here's the cod...

Performance with SQL linked tables
Hi, My question is a application performance issue so maybe the wrong forum. I have a SQL Server 2005 Express database and connected Access 2003 clients. The performance seems to have degraded since moving from SQL 7. The users are seeing delays when clicking on menu optiosns that have open bound forms in Access. So I have been using the Profiler to have a look at things. I am filtering events SQL:Batchcompleted and RPC:Completed with duration > 10ms. I found some issues with the forms loading without filerting rows so i have changed it so it does not load any rows on opening. A...