Display last Record based on person

[Minty]

If you have imported or linked to data in Sage in Excel, then you can do exactly the same directly in Access.
This would remove the need completely for the triple entry.

If you have mastered a SQL query in Excel then the same query would work in access.

Your issue with updating records sounds like simply not setting up a form to "not allow edits" when it is only used for viewing data?

 

[bartmanekul]

Yeh, I thought the same when I thought of taking up brain surgery. How hard can it be right?, just a few sharp scalpels and a few clamps should do the trick. I've cut a few things to shape in the garage in the past and superglued them back together.

Then I found out that if you cut in the wrong place, you could do a lot of damage, and as you could never have a backup, I decided to forget that idea.

FWIW I know of people who think anything but a simple formula in Excel is for the technical people.?

Horses for courses.

Very funny....I'm not comparing it to brain surgery or rocket science!

I went from knowing a maximum of what dollar signs do to being able to pull data from sage and do all sorts in excel - but I don't get why Access is so horrible to work with.

Perhaps it's because excel seems far more polished than access? Meh. This is going to have to be a spare time thing, which is a real shame, I know it'd save hours at work for various teams.

 

[bartmanekul]

If you have imported or linked to data in Sage in Excel, then you can do exactly the same directly in Access.
This would remove the need completely for the triple entry.

If you have mastered a SQL query in Excel then the same query would work in access.

Your issue with updating records sounds like simply not setting up a form to "not allow edits" when it is only used for viewing data?

No...the issue is awkward, with this one I don't need to link anything to sage/access. I'm trying to negate something else.

The form is for adding a record - but knowing the users it HAS to only update on a confirmation (i.e. add new record), rather than just maybe putting one thing in and closing it etc.

I found this: https://www.access-programmers.co.uk/forums/threads/saving-a-record-only-via-a-save-button.287336/

But it doesn't seem to work. I assume button name is under 'Name' in the properties.

 

[Gasman]

Very funny....I'm not comparing it to brain surgery or rocket science!

I went from knowing a maximum of what dollar signs do to being able to pull data from sage and do all sorts in excel - but I don't get why Access is so horrible to work with.

Perhaps it's because excel seems far more polished than access? Meh. This is going to have to be a spare time thing, which is a real shame, I know it'd save hours at work for various teams.

No. One of the hardest things people have to do when moving from Excel to Access, is to lose their 'Excel way' of doing things.
Once you grasp the concept of databases and how they are meant to work it gets easier. Still a lot to learn, but easier.

If you stick at it, after a while you will wonder why you thought Access was so difficult.

Eg, if you create a basic form using the wizard, you can add records immediately. So why you have difficulty adding records is puzzling, as you must have changed something.?
Another thing to get used to (and this took me a while) is that by default forms are 'bound' meaning attached to table/query. When you amend a control on a form, that will update it's source field when you move off the record. So many people want to have an add record/save record button in access, but I tend to use the default behaviour.

 

[bartmanekul]

No. One of the hardest things people have to do when moving from Excel to Access, is to lose their 'Excel way' of doing things.
Once you grasp the concept of databases and how they are meant to work it gets easier. Still a lot to learn, but easier.

If you stick at it, after a while you will wonder why you thought Access was so difficult.

Eg, if you create a basic form using the wizard, you can add records immediately. So why you have difficulty adding records is puzzling, as you must have changed something.?
Another thing to get used to (and this took me a while) is that by default forms are 'bound' meaning attached to table/query. When you amend a control on a form, that will update it's source field when you move off the record. So many people want to have an add record/save record button in access, but I tend to use the default behaviour.

I've worked with databases before, so I'm reasonably familiar with how they should be set up etc, and linking tables and so forth. More so, the need for clean data and restricting the ways they can be entered.

Access tends to be so unforgiving.

I'm not having trouble adding records - as said, I don't want it to add until a button is pressed. Because I know some of the users in here will half fill out a form, then get distracted by a call and likely move off. Then loads of partial records will be in there. This is why access isn't great, there should be an easy way to set this up, but there isn't. The code I've looked at just doesn't work.

Regarding the original problem, even the simplest code doesn't work! Access deeply sucks for the simple things.

I'm trying to add a simple info display on my form.

Textbox, with expression:

=DLast("Quote Number","Table1","Person = Luke")

Quote number is the field name, table1 is...well, obvious. And Person is the field name with Luke being in the records.

I want to display the quote number from the last record where the person was Luke. But it simply says error. As far as I can tell, the above should work.

 

[Minty]

Textbox, with expression:

=DLast("Quote Number","Table1","Person = Luke")

Quote number is the field name, table1 is...well, obvious. And Person is the field name with Luke being in the records.

I want to display the quote number from the last record where the person was Luke. But it simply says error. As far as I can tell, the above should work.

This won't work and wouldn't work in excel either. Try
=DLast("[Quote Number]","Table1","Person = 'Luke'")
Notice the extra single quotes around Luke indicating it's a string. I have added square brackets around the field name as it has a space in it, which can cause a problem.

Access by default saves information entered into bound controls, because in 95% of uses that is what end-users want.
You can easily stop half entered records being saved by using the Forms Before_Update event.

How many fields are required and what are the controls called?

 

[bartmanekul]

This won't work and wouldn't work in excel either. Try
=DLast("[Quote Number]","Table1","Person = 'Luke'")
Notice the extra single quotes around Luke indicating it's a string. I have added square brackets around the field name as it has a space in it, which can cause a problem.

Access by default saves information entered into bound controls, because in 95% of uses that is what end-users want.
You can easily stop half entered records being saved by using the Forms Before_Update event.

How many fields are required and what are the controls called?

Ahhh so it does tie in with some of excel's requirements. All the examples I was looking at didn't have any of that in.

Thank you, that works. I've now got it looking at the drop down list of names on an entry box on the same form. It works, but doesn't refresh if the selection is changed. Not a biggie, but if it's a simple change to one of the properties then great.

With the fields required on the forms, there's about 11 or 12. A few might not required to have entries.

I'm guessing you'd be on about the example further on down listed here, preventing it from going on if you don't enter data into certain fields:

Form.BeforeUpdate event (Access)

Office VBA reference topic

docs.microsoft.com

 

[jdraw]

The BeforeUpdate event of the form is the last place where you can check/validate/verify entry of data before a record (or partial record) will be saved.
So you use a procedure in this event to ensure all of your 11 or 12 controls have appropriate data values. If 1 or more is empty/doesn't meet your validation requirements, then you can Display a Message - prompt for proper value(s)- or some other action that you define.

 

[bartmanekul]

The BeforeUpdate event of the form is the last place where you can check/validate/verify entry of data before a record (or partial record) will be saved.
So you use a procedure in this event to ensure all of your 11 or 12 controls have appropriate data values. If 1 or more is empty/doesn't meet your validation requirements, then you can Display a Message - prompt for proper value(s)- or some other action that you define.

Thanks, I will look at examples of that one and customise.

 

[Gasman]

If you have a lot of controls to validate then using the tag property of the controls is one approach.?

A few threads to review?

https://www.access-programmers.co.uk/forums/search/160265/?q=beforeupdate+tags&o=relevance

HTH

 

[Minty]

With the fields required on the forms, there's about 11 or 12. A few might not required to have entries.

I'm guessing you'd be on about the example further on down listed here, preventing it from going on if you don't enter data into certain fields:

Form.BeforeUpdate event (Access)

Office VBA reference topic

docs.microsoft.com

@jdraw and @Gasman beat me to it. Yes!

I have routines that will put a msgbox up listing the missing fields, highlight colour them and/or give the user the option to exit without committing the changes they haven't finished.

It can be handy to use the controls Tag property to identify if it's required or not and loop though them rather than writing a ton of code for each control separately.
If you need assistance after having a go let us know.

 

[bartmanekul]

Thanks.

I've tried doing it, but I'm not up on the finer details (FWIW I've never learnt to code due to dyslexia).

I assume this is what I need, I've tagged the fields which are required with 'Rqd'. But not sure how to substitute it into the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim oContr As Control
    For Each oContr In Me.Detail.Controls
        If IsNull(oContr) = True Then
            If MsgBox(oContr.Name & " is empty", vbOKCancel) = vbCancel Then
                Cancel = True: oContr.SetFocus: Exit Sub
            End If
        End If
    Next oContr
End Sub

I can't quite understand what the oContr/Control refers to, but I'm guessing one might be the tag?

 

[Minty]

That's not quite right as it's not checking for the tag value try something like

 Dim sMsg As String
    Dim ctl As Control
       
    For Each ctl In Controls
        If ctl.Tag = "Req" Then
            If IsNull(ctl) Then
                sMsg = sMsg & ctl.Name & vbCrLf
            End If
        End If
    Next ctl
               
    If Len(sMsg & "") > 0 Then
   
        If msgBox("Unable to update records without the following information ;" & sMsg & vbCrLf & vbCrLf & "Please enter data or cancel this record!", vbCritical + vbOKCancel, "Missin Data!" ) = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        Else
            Cancel = True
            Me.SomeControlthatNeedsYourAttention.SetFocus
            Exit Sub
        End If
    End If

 

[jdraw]

See the link from Gasman in this post re use of Tag in Validating form controls.

 

[Isaac]

Thanks for the code, sadly it appears access is a horrific program compared to excel, and is well beyond me. I don't even know what to do with the code you posted. Thought I could learn but it's not proving very friendly. It's frustrating as I've been able to work out pulling data from sage and adjusting SQL queries (when I don't know SQL).

Access just seems so damn difficult and missing so many basic features.

My next hope is to bring up the last [Quotenumber] based on the [Person], who will add their name from a drop down.

Sadly I have no idea how to do this. It needs to look at the [Person] they have chosen from the drop down in the current record they are entering.

Another thing which shows access is unfriendly - there's no easy way to stop it updating records unless you press the add record button. I've found code but that isn't easy to impliment. Grrr.

Well ... I thought I covered all the bases with my response, but let me try to add some?
Maybe you could add that code to the combobox's AfterUpdate event? That means when someone picks something in the drop down, your code will run.
Obviously, look at the code and think of where you will need to change names, for example, if I put Me.Controlname.Value, you might have to put Me.Textbox1.Value

 

[Pat Hartman]

Access just seems so damn difficult and missing so many basic features.

Access isn't a toy no matter what you have been led to believe and it really isn't missing any basic features. you just haven't learned how Access works yet. The fact that Access has been around for over 25 years in essentially the same form although with enhancements over the years should tell you that it is actually a superior tool. Bad tools wither away because people don't find them useful.

Another thing which shows access is unfriendly - there's no easy way to stop it updating records unless you press the add record button. I've found code but that isn't easy to impliment. Grrr.

You actually have absolute control over whether or not Access saves a record. Again, you need to understand some Access basics. One of the basics is that Access is a RAD (Rapid Application Development) tool and that means that it is doing a great deal of the grunt work of creating the application behind the scenes. All bound forms (forms where the RecordSource is a query or a table rather than blank) automagically save records by default. And it SHOULD since that is the behavior most useful to novices. If you want to control that, you will need to use the Form's BeforeUpdate event. Think of this as the tip of a funnel through which ALL records have to pass before being saved. It is the last event that runs before a record is saved and it ALWAYS runs if the record is dirty (changed) and there is nothing you can do to stop it. That's the RAD part. The custom part which is where you learn to control Access is you put your validation code in this event and if the record is not valid, you can prevent it from being saved. For example. Say you have an employee form and you want to require a LastName and a hire date but everything else is optional.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Proc

    If Me.LastName & "" <> "" Then  'name is not empty
    Else
        Cancel = True
        MsgBox "Last Name is required.", vbOKOnly + vbInformation
        Me.LastName.SetFocus
        Exit Sub
    End If

    if IsDate(Me.HireDT)
        If Me.HireDT <= Date() +21 Then  ' hire date cannot be more than 21 days in the future'
        Else
            Cancel = True
            MsgBox "Hire Date cannot be more than 21 days in the future.", vbOKOnly
            Me.HireDate.SetFocus
            Exit Sub
        End If
    Else
        Cancel = True
        MsgBox "Hire Date is required.", vbOKOnly + vbInformation
        Me.HireDate.SetFocus
        Exit Sub
    End If
    Me.UpdatedBy = Environ("UserName")
    Me.UpdatedDT = Now()
Exit_Proc:
    Exit Sub
Err_Proc:
    MsgBox Err.Number & "--" & Err.Description, vbCritical
    Resume Exit_Proc

End Sub

You might want to sit down with a basic book and read about objects( there are only 6 of them in the current version) and what they are used for.

Four things are critical to making progress.
1. Having a basic understanding of how to design a database schema. Read about normalization to help you. If your table design is flawed, you will have nothing but trouble trying to make a working application.
2. Learn the basics of queries to retrieve data because once you normalize your data, you will be using queries with joins to bring it all back together again into "information".
3. Understand Form and Control events. Each event has a purpose. Understand why the event is there and you'll know how to use that event to control the application. I explained the Form's BeforeUpdate event which in my opinion is the most important event of a form.
4. Search for a list of VBA functions sorted by category. Much of the simple code you will need will use these canned functions.

 

[bartmanekul]

Thanks for all the replies, just wanted to give an update.

I thought a lot about it and the data, and while I can't deviate from the reference numbers, I can change the names (as there's only a few users). So I've renamed the 'People' to match the windows logons, and display the last record based on the user logon. It seems to be the easiest and most robust option.

Isaac, something you said gave me this idea, thanks for that

I have to disagree with access being good - it's missing some basics imo. I know I'm up against long time users that have seen it evolve over time, but for it's place - a 'basic' database it could have an easier interface and flesh out some things. I'm sure most on here will disagree on that , but that's my opinion. Control is one thing, having to write code for some basic things is a different matter.

I've put something together, I do know normalisation, and data handling (most specifically how to shape it looking at the future in order for ease of pulling data). My main consideration is ease of use - going to pains on normalisation is pointless if people don't use it. As said this isn't going to be large, and on much I am restricted so hopefully the less desirable aspects of the data shouldn't be an issue.

Again, thanks to all commenters on this thread, the help is very much appriciated.

 

[Isaac]

Thanks for all the replies, just wanted to give an update.

I thought a lot about it and the data, and while I can't deviate from the reference numbers, I can change the names (as there's only a few users). So I've renamed the 'People' to match the windows logons, and display the last record based on the user logon. It seems to be the easiest and most robust option.

Isaac, something you said gave me this idea, thanks for that

I have to disagree with access being good - it's missing some basics imo. I know I'm up against long time users that have seen it evolve over time, but for it's place - a 'basic' database it could have an easier interface and flesh out some things. I'm sure most on here will disagree on that , but that's my opinion. Control is one thing, having to write code for some basic things is a different matter.

I've put something together, I do know normalisation, and data handling (most specifically how to shape it looking at the future in order for ease of pulling data). My main consideration is ease of use - going to pains on normalisation is pointless if people don't use it. As said this isn't going to be large, and on much I am restricted so hopefully the less desirable aspects of the data shouldn't be an issue.

Again, thanks to all commenters on this thread, the help is very much appriciated.

I am definitely a huge proponent of capturing the network/windows username and letting it drive many other things--it's the way to go in many environments I think. Glad to hear it helped.

Access certainly leaves plenty to be desired, you can always leave suggestions on Access uservoice.