Forms- getting last instant of data and putting it into next, in a one to many relationship using VBA

[farmerscott]

Hi Guys,

Using Access 2007 with low to intermediate skills in VBA.

I have a situation where I enter a lot of data that often repeats itself, but I can't "hard wire" the values. I hope that makes sense.

I want to be able to click over (on the many side of the form) and have a checkbox that says "Do you want to repeat the values?" The values would be pasted in and I could then make any minor adjustments. The start of the code might look like-

Private Sub Check58_Click()
Dim DateFilled As Date
Dim DateSprayed As Date

If Check58 = True Then
DateFilled = Date_Filled.Value 'get last instance from previous entry.
DateSprayed = Date_Sprayed.Value 'get last instance from previous entry.

End If
End Sub

Any thoughts on how you might approach this? I am thinking I can save the data (in VBA) and then paste it into the new/empty form.

cheers

FarmerScott

 

[welshgasman]

The way I have done this in the past is to have a checkbox on the form.
Then code in the afterupdate event of any controls where I want to copy over the data.

Below is a sample of my code.

HTH

Private Sub Date_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Date_ID].DefaultValue = """" & Me![Date_ID].Value & """"
    End If
End Sub


Private Sub Rank_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Rank_ID].DefaultValue = """" & Me![Rank_ID].Value & """"
    End If


End Sub

 

[Micron]

But you want to copy over the same data multiple times, right?
And the many side is a subform with a set of records, but one field is either blank or you want to edit?
Or it is a subform, but there are no records and you want to copy over one value many times and create that many records?
Or...??

 

[farmerscott]

Welshgasguy and Micron,

Thanks for your input/comments.

Essentially I want some code that will copy data on a subform. When I make an instance of a new subform (but still relating to the the same main/parent form) I want the data automatically inputted.

The checkbox code above looks to be copying the data, I now need some change event (eg new instance of a subform) that will input the data.

Another basic coding idea I have is....on the new/empty subform a checkbox/messagebox is used in conjuction with Dlast or DFirst to get the most recent data from a table.


Hope that helps,

FarmerScott

 

[welshgasman]

Well it does, but as I tab through the controls and get on to the new record the controls are automatically populated.

I created this code as I created a database for a website of mine
The structure of the table was
ID
DateID
ShipID
RankID
CrewID

The ShipID would remain the same for about 12 to 15 crew and the DateID would remain the same for about 20 ships.
Cut down on the data entry a hell of a lot. :D

The checkbox code above looks to be copying the data, I now need some change event (eg new instance of a subform) that will input the data.

TBH I think this process is exactly what you want, unless I have misunderstood your requirements.?

 

[farmerscott]

Hi Welshgasman,

Sorry, I underestimated your code. Still have a lot to learn about VBA....


Some quick questions...

1. Where does the code go? In the module or in the form?

2. We are making an after-update change event for each control we want to copy? (I assume we can't bundle all these together in a single change event as they are being updated at different times.)

3. Should

If Me.chkCopy Then

be

If Me.chkCopy=true Then

4.

Me.

is a reference to a form?

5. Why the double quotes and use of the &?

"""" & Me![Rank_ID].Value & """"

6. Will this work on continuous forms?

cheers

FarmerScott

 

[welshgasman]

1. I put mine in a form. I suppose you could put it in a module and pass the form and control, but start small and work your way up. :D
2. That is just the way I did it. I am new to Access as well. You could try putting the code in the afterupdate event of the form for all controls. I only had two after all.
3. You can write it that way, but my way means the same and less typing.
4. Yes, the form you are on.
5. That is just what is needed for it to work. Both those controls hold a numeric ID, but you still need them. I gleaned that from a Google. Another way Microsoft Access tips: Assign default values from the last record
6. Not sure, not used them yet. Did try, but found code for one record affected others, so stuck to datasheet forms.

HTH

Edit. Reviewing 5 this link https://www.pcreview.co.uk/threads/repeat-data-on-next-record.3169504/ seems to indicate that quotes are not needed for numeric data, which I am now aware of, but it works, and that data is definitely numeric. The table just consist of 4 ID fields to link Ships,Date,Rank and Crew.

I might review that, but the DB is complete now.

Hi Welshgasman,

Sorry, I underestimated your code. Still have a lot to learn about VBA....


Some quick questions...

1. Where does the code go? In the module or in the form?

2. We are making an after-update change event for each control we want to copy? (I assume we can't bundle all these together in a single change event as they are being updated at different times.)

3. Should

If Me.chkCopy Then

be

If Me.chkCopy=true Then

4.

Me.

is a reference to a form?

5. Why the double quotes and use of the &?

"""" & Me![Rank_ID].Value & """"

6. Will this work on continuous forms?

cheers

FarmerScott

 

[farmerscott]

Hi Welshgasman,

thanks for the detailed response. Much to test, learn and think about.

Will get back to you..

cheers

FarmerScott

 

[Micron]

and that data is definitely numeric.

This is a tricky one. It looks like a duck, and sounds like a duck but it might be a chicken. You have to go by the data type property, not how the data looks. If the field data type is text and the value is 123, quotes are needed. If the type is long (or any other numeric type) you will raise a 'data type mismatch' error if you use quotes around a number. You can run the function IsNumeric (123) or IsNumeric ("123") and the answer is TRUE in both cases, even though quotes are used. That's because the function evaluates whether or not the value being tested resembles a number. It does not prove that the data type is numeric. To make matters worse, Isnumeric (a123) is TRUE, while IsNumeric ("a123") is FALSE. I can't explain that one.

Furthermore, text data type isn't the best choice for numerals but sometimes it can't be avoided. It introduces sorting issues, because numerically, 20 comes before 100 when the type is a number, but 100 comes before 20 if the type is text. Usually, you have to introduce some kind of sorting field to get around it.

 

[welshgasman]

All I can say is the form is based on a table that has only 4 keys, all long integer, and it works. Now not sure how, but it works.

I can't find where I got it from now. I'll make a note of this for my next project, but I am going to leave the the db as it is.