473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Setting a value to a field in a new row to match the last row in a form

10
Hello

Seems to be an easy problem but I am a newbee in VBA.
I have a form with a list of resources, I want to set the value of one of the fields (type) to match the same field in the previous row when clicking on a new row button.

Any ideas ?
Jul 28 '10 #1

✓ answered by missinglinq

The question is whether these records are being entered in a single session, i.e. the form being opened, then one record after another after another, etc, being entered. If so using a hack that sets the DefaultValue for the field, as NeoPa alluded to, is the way to go.

An example of that would be
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourTextBoxName_AfterUpdate()
  2.   Me.YourTextBoxName.DefaultValue = """" & Me.YourTextBoxName.Value & """"
  3. End Sub
If, on the other hand, multiple sessions are involved, with the form being opened, a record or records being entered, the form being closed then re-opened at a later date, and more records entered, this method won't work. DefaultValues assigned thru code will be 'lost' when the form is closed.

In this latter case I think you'd have to use a combination of DLookup() and DMax() against an auto-incrementing ID field or against a timestamp field, or, alternately, store the value in a utility table when the form is closed and retrieve it and assign it to the DefaultValue each time the form is opened.

Welcome to Bytes!

Linq ;0)>

12 1685
NeoPa
32,556 Expert Mod 16PB
Do you need this to default? Or would Ctrl-' do you?

When entering data into a datasheet (This works in Excel as well as Access) hold down Ctrl while tapping ' (Ctrl-') to replicate the value from the cell above.
Jul 29 '10 #2
edme
10
@NeoPa
is there a way to do this to specific fields automatically when creating a new record ?
Aug 1 '10 #3
missinglinq
3,532 Expert 2GB
The question is whether these records are being entered in a single session, i.e. the form being opened, then one record after another after another, etc, being entered. If so using a hack that sets the DefaultValue for the field, as NeoPa alluded to, is the way to go.

An example of that would be
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourTextBoxName_AfterUpdate()
  2.   Me.YourTextBoxName.DefaultValue = """" & Me.YourTextBoxName.Value & """"
  3. End Sub
If, on the other hand, multiple sessions are involved, with the form being opened, a record or records being entered, the form being closed then re-opened at a later date, and more records entered, this method won't work. DefaultValues assigned thru code will be 'lost' when the form is closed.

In this latter case I think you'd have to use a combination of DLookup() and DMax() against an auto-incrementing ID field or against a timestamp field, or, alternately, store the value in a utility table when the form is closed and retrieve it and assign it to the DefaultValue each time the form is opened.

Welcome to Bytes!

Linq ;0)>
Aug 1 '10 #4
ADezii
8,834 Expert 8TB
@edme
If you wish to retrieve the Value from a specific Field, from the prior Record for a New Record, and Insert it into the Field, you can do this in the GotFocus() Event. The following code will Insert the Value from the Region Field from the Previous Record into the Current (New) Record when the Field receives the Focus.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Region_GotFocus()
  2.   If Me.NewRecord Then
  3.     SendKeys "^'", True
  4.   End If
  5. End Sub
Aug 1 '10 #5
missinglinq
3,532 Expert 2GB
Using SendKeys really should be avoided unless there is absolutely, positively no other way to accomplish a task, which is not the case here.

SendKeys is unreliable under a number of Windows/Access version combinations, with reports of it not working at all under Vista and not always working under Access 2007/2010. There is also a problem with it toggling the NumLock and CapsLock keys in some instances.

Linq ;0)>
Aug 1 '10 #6
parodux
26
I believe the best way to do this is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  Dim rs As DAO.Recordset
  3.   If Me.NewRecord Then
  4.       Set rs = Me.RecordsetClone
  5.       rs.MoveLast
  6.       Me!NameOfField.DefaultValue = rs!NameOfField
  7.       rs.Close
  8.   End If
  9. End Sub
  10.  
Aug 1 '10 #7
NeoPa
32,556 Expert Mod 16PB
You don't really want line #7 (although I understand the laudable impulse).

It's generally good practice to leave objects as you find them. If you open a recordset, then it's right to close it when finished. If, as in this case, you find it open but assign a new object to refer to it, then it should be left open, but the new object link should be cleared (Set rs = Nothing) when done.

This is probably not a serious problem (I think the close would be ignored anyway) but the point is worth making for the understanding. Ultimately all objects are cleared at the end of the procedure anyway, but I'm guessing you like your code to be tidier than that, which I fully understand and agree with.

As far as the issue is concerned, I would have thought it would be less code intensive to change the default as and when a change is made on the form (ControlName_AfterUpdate()). The Form_Open() event procedure could be used to set it if that were deemed necessary.
Aug 2 '10 #8
edme
10
Thanks all,
I actually used @missinglinq advise and it worked perfectly.
Aug 2 '10 #9
parodux
26
@NeoPa
I think I get your point!~)

Anyway, yes.. I do like to keep my code tidy and the Set rs = Nothing I just forgot, I normally use that to.

..About the afterupdate... edme just wants a value from the last post when in a new post. So if you open the form and goto new post you need to pick up the value from the last post on the way!~)
Aug 2 '10 #10
NeoPa
32,556 Expert Mod 16PB
parodux: ..About the afterupdate... edme just wants a value from the last post when in a new post. So if you open the form and goto new post you need to pick up the value from the last post on the way!~)
This is not always required. It is not clear from the question weather or not a New Record after opening the form should have any default set. If it is required then the Load event would be an appropriate place for it. I cannot think of any situation where AfterUpdate code would be required in either scenario.

BTW I had a strong suspicion you were a tidy coder as only tidy coders would even have thought of closing .RecordsetClone, even via a recordset variable.
Aug 2 '10 #11
parodux
26
@NeoPa
You mentioned the ControlName_AfterUpdate() that's all..

I wouldn't use it!~)
Aug 2 '10 #12
NeoPa
32,556 Expert Mod 16PB
I do apologise. You must be wondering what I'm smoking. I'm afraid I got confused as to what I was talking about.

Let me clarify that my original statement (post #8) was what I can stand by and the follow up (post #11) was wrong. After the initial logging of the value when the form is loaded, the .DefaultValue need only be changed when a value in that control is updated.

Sorry again for any confusion.
Aug 3 '10 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Jacob | last post by:
I have a page Add_data.asp which accepts a number of user-entered variables and then inserts them into a database. I have another page, Loc_tree.asp which when accessed, returns a tree of...
1
by: Colin Colin | last post by:
I downloaded a calendar.asp file that someone named Jacob "WickedPisser" Gilley made. It's a few years old but It works fine and it's what I was looking for without getting into ActiveX objects. ...
6
by: WindAndWaves | last post by:
Hi Gurus In my quest in putting my first javascript together, I am now trying to conquer something that seems trivial, but has taken me hours. I would like to format a field in a form once the...
1
by: james00_c | last post by:
Greetings- I need to pass multiple email addresses to a "sendto" hidden field in a form. I need to do that because "CC" and "BCC" are not an option. One address webmaster@xyz.com would be...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
4
by: elfyn | last post by:
I'm currently developing a Fleet system which enables the user to enter Parts used to Service a vehicle. The parts are available through a Combo Box on a form and if the user enters a new Part the...
3
by: Don Stevic | last post by:
Hey there I have beentrying to get a windows form to send an email thru outlook and cannot find the right code. Does anyone know how to do this? Thanks *** Sent via Developersdex...
13
by: abcdefghijklmnop | last post by:
I have a "Questions" form that reads off of a table and contains a multi-value field called "Keywords". I already know that having a multi-value field is a horrible idea, however, I am patching up a...
9
by: Haas C | last post by:
Hi all! Is there anyway I can override a value in a calculated field on a form? For example: I have a form which displays the following fields based on a query: Premium Due field has the...
15
by: amy1 | last post by:
Hello everyone, I'm new here and new to Access2007 as well! I have a multi-value field in a form, and would like to calculate the total of the selected values in this field and place the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.