Saving changes to records on a form

[AdmiralN]

Hi,

I have a form that is a combination of comb boxes and text boxes which functions as a means to to lookup records, taking the information from several different tables. The text boxes are all populated via Dlookup in an after update event from a combo box.

What I want to be able to do is to be able to edit and add information to records where some of the text boxes are not populated. What would be the best way to achieve this?

Thanks in advance for help with this issue.

[twinnyfo]

AdmiralN,

I think we can help you with this, but we will need considerably more detail before we can start.

First, it's not entirely clear what it is you are describing with [qoute]What I want to be able to do is to be able to edit and add information to records where some of the text boxes are not populated.[/quote]

In general, editing and saving information to records is relatively easy, but you may have thrown us a curve in your description.

Thanks for any additional information you can provide.

[AdmiralN]

Hi Twinnyfo,

Thanks for your response. Hopefully I can explain my issue a bit clearer below.

To give a bit of background, for my database I started with importing a single sheet excel file. This is my first database so I'm aware I've probably made some mistakes with how I've set this up.

I currently have 4 tables, tblActvity, tblID, tblPackages and tblUnitPeriod. I also currently have a single unbound form which contains 2 cascading combo boxes cboPackageNumber and cboActivity, which serve to populate a number text boxes with the rest of the information for each record from each table. Below is the code I'm using for the after update event.

Option Compare Database
 
Private Sub btnAddNewRate_Click()
 
End Sub
 
Private Sub cboActivity_AfterUpdate()
    Me.cboRateHigh = _
        DLookup("ProductivityRateHigh", _
                "tblUnitPeriod", _
                "ID=" & Me.cboActivity)
    Me.cboRateLow = _
        DLookup("ProductivityRateLow", _
                "tblUnitPeriod", _
                "ID=" & Me.cboActivity)
    Me.cboUnit = _
        DLookup("Unit", _
                "tblActivity", _
                "ID=" & Me.cboActivity.Column(1))
    Me.cboTimePeriod = _
        DLookup("[Time Period]", _
                "tblActivity", _
                "ID=" & Me.cboActivity.Column(1))
    Me.cboSource = _
        DLookup("Source", _
                "tblID", _
                "ActivityID=" & Me.cboActivity.Column(1))
    Me.cboNotes = _
        DLookup("Notes", _
                "tblID", _
                "ActivityID=" & Me.cboActivity.Column(1))
    Me.cboMax = _
        DLookup("LeadTimeHigh", _
                "tblID", _
                "ActivityID=" & Me.cboActivity.Column(1))
    Me.cboMin = _
        DLookup("LeadTimeLow", _
                "tblID", _
                "ActivityID=" & Me.cboActivity.Column(1))
 
    If cboPackageNumber > 0 Then
        cboPackageNumber.Enabled = True
        cboActivity.Visible = True
        Me.cboRateHigh.Requery
        Me.cboRateLow.Requery
        Me.cboUnit.Requery
        Me.cboTimePeriod.Requery
        Me.cboSource.Requery
        Me.cboNotes.Requery
        Me.cboMax.Requery
        Me.cboMin.Requery
    Else
        cboActivity.Enabled = False
    End If
 
End Sub
 
Private Sub cboPackageName_AfterUpdate()
 
End Sub
 
Private Sub cboPackageNumber_AfterUpdate()
    Dim strSql As String
 
    strSql = _
        "SELECT o.RateID, o.ID, o.Activity, o.[Time Period], o.Unit " & _
        "FROM tblActivity AS o " & _
        "WHERE o.PackageID = '" & cboPackageNumber.Value & "'" & _
        "ORDER BY o.PackageID;"
    Debug.Print strSql
    cboActivity.RowSource = strSql
 
    If cboPackageNumber > 0 Then
        cboPackageNumber.Enabled = True
        cboActivity.Visible = True
        Me.cboActivity.Requery
    Else
        cboActivity.Enabled = False
    End If
 
End Sub
 
Private Sub Form_Current()
   Me.saveRecord.Enabled = False
End Sub
 
Private Sub Form_Dirty(Cancel As Integer)
    Me.saveRecord.Enabled = True
End Sub
'------------------------------------------------------------
' btnAddnew_Click
'
'------------------------------------------------------------
Private Sub btnAddnew_Click()
On Error GoTo btnAddnew_Click_Err
 
    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
btnAddnew_Click_Exit:
    Exit Sub
 
btnAddnew_Click_Err:
    MsgBox Error$
    Resume btnAddnew_Click_Exit
 
End Sub
 
'------------------------------------------------------------
' btnSaveRec_Click
'
'------------------------------------------------------------
Private Sub btnSaveRec_Click()
    If Me.Dirty = True Then
        DoCmd.RunCommand acCmdSaveRecord
    Else
        MsgBox "nothing to save"
    End If
On Error GoTo btnSaveRec_Click_Err
 
    DoCmd.SetWarnings False
2
'This will save a new record:
3
    DoCmd.RunSQL "INSERT INTO tblID(LeadTimeHigh) VALUES('" & cboMax & "')"
4
'This will save value to existing record:
5
    DoCmd.RunSQL "UPDATE tblID SET LeadTimeHigh='" & cboMax & "' WHERE ID=" & cboMax
6
    DoCmd.SetWarnings True
 
    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="btnAddnew" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="OnError"/><Action Name="GoToRecord"><Argument Name
    ' _AXL:="Record">New</Argument></Action><ConditionalBlock><If><Condition>[MacroError]&lt;&gt;0</Condition><Statements><Action Name="MessageBox"><Argument Name="Message">=[MacroError].[Description]</Argument></Action></Statements></If></ConditionalBlock></
    ' _AXL:Statements></UserInterfaceMacro>
    On Error Resume Next
    DoCmd.RunCommand acCmdSaveRecord
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
btnSaveRec_Click_Exit:
    Exit Sub
 
btnSaveRec_Click_Err:
    MsgBox Error$
    Resume btnSaveRec_Click_Exit
 
End Sub

I named the text boxes with cbo prefix instead of txt just to make it more confusing:)

What I would like to be able to do is update the records from the form where some of the text boxes are blank. I would also like to be able to add new records. I've tried adding a 'Save' button with the wizard and an 'Add New Record' button with the wizard but currently these buttons don't seem to do anything. I understand this is because my form is unbound.

I hope that makes more sense. Thank you again for any help you can provide.

[twinnyfo]

To say that there is a "lot" wrong with your code would be an understatement, but please don't take that as an "eye poke", as you state you are new to this adventure. Let me try to start at the beginning, line by line (with the adjusted code above), and then ask a few more questions to try to make sense out of all this.

Line 2 should be:

Option Explicit

For whatever unconscionable reason, Microsoft does not default to forcing us to declare variables when we use them. This is a TERRIBLE habit to get into. From your VBA Editor, Tools | Options | Editor Tab | make sure "Require Variable Declaration" is checked.

Lines 8-39: It appears you are merely assigning values to combo boxes, but it doesn't appear that you are using these combo boxes anywhere else in your code (or very little of them). First, if you are merely assigning a value to the combo box (and not using the combo box as a combo box), why not just use text boxes? Again, I don't know everything that's going on with this form, but it appears you posted the code from the entire form? Second, several of the combo boxes have data from the same table. You are forcing the DB to query the entire table (in some cases) four times to access one recod on the same table. Again, you're not using this data for anything, but a more efficient way would be to use a recordset. Additionally, combo boxes usually have (at least) two columns of data, the first is an index or key (usually hidden) and the second (and following) values that make sense to the user. The "value" of a combo box is usually the index that is then used for other purposes. Lots here to understand about combo boxes and indexes.

Upon further review after finishing this entire response and re-re-re-reading your latest post:

AdmiralN:
I named the text boxes with cbo prefix instead of txt just to make it more confusing:)

As someone who's been doing this for quite a few years--yes, lots of grey hairs--and as a moderator, who's trying not to get kicked off a forum he's done a lot to help others on, all I can say (and this is the tamest language I can force myself to use at this point) is that this is the second most asinine thing I have ever seen someone do. The most asinine thing I've ever seen is a guy who designed a massive database in which all his table names and field names were jumbled 8-character puzzles, because, so he says, "He didn't want anyone to know what was in the tables and fields." As you can observe from the previous paragraph I wrote concerning combo boxes (and my COUNTLESS references to your "combo boxes" throughout), your naming convention matters. When you name a control cboWhateverYouWannaCallit, anyone reading this site is going to make one assumption: it is a combo box. Consider this your one and only repreive for being cute--I'm in a good mood today.

Line 41: cboPackageNumber does not appear to be changed or modified anywhere in the previous code, so it's value never changes. Yet, in lines 62ff., you have some code evaluating cboPackageNumber, performing much of the same activities, including making cboActivity visible if cboPackageNumber is greater than 0. I would argue that this evaluation in line 41 is unnecessary, as if the code ever gets to line 41, by definition, cboActivity is visible, because it was updated. More on this section of code below.

Line 42: cboPackageNumber was updated in order to get here, so it is already enabled and is never disabled anywhere.

Line 43: cboActivity was just updated to execute this line of code. By definition, it is visible.

Lines 44-51: Since you are merely assigning values to the combo boxes, and not assigning a new Record Source, there is no need to requery these controls.

Line 53: Again, since cboPackageNumber has already proven to be greater than 0 (to get here), this line will never be executed.

Lines 65-69: Again, a lot that is out of the ordinary here. While it is possible for you to assign many columns to a combo box, we would expect you to use them. In this case, you are using five columns, but only appear to use two. Some additional notes on this section:

First, the SQL itself. If you are querying from only one table, it is not necessary to refer to the fields explicitly using the TableName.FieldName syntax. Don't get me wrong, this may be a good habit to get into as you design more complex queries. Additionally, it certainly appears that cboPackageNumber stores a numerical value, yet you are using single quotes, implying that it is text (which can throw the DB off sometimes). Also, there is essentially never a reason to refer to a control's .Value property, as it is the default reference. Thus, for such a simple query, your SQL could be as easy as:

    strSql = _
        "SELECT RateID, ID, Activity, [Time Period], Unit " & _
        "FROM tblActivity " & _
        "WHERE PackageID = " & cboPackageNumber & _
        " ORDER BY PackageID;"

You may also observe that the Field [Time Period] requires square brackets around it. This is because Access doesn't like Fields with spaces, and this is how it treats the text as a single field. This is encouragement for us to create tables with fields that do not contain spaces. The Field Name "TimePeriod" would work equally well, and not require brackets.

Second, you are pulling this data from tblActivity, yet you are using Domain Query Functions (DLookup) to find data from tblActivity in two of your Combo Boxes (Unit and [TimePeriod]). You could use the value currently in cboActivity to update these combo boxes:

    Me.cboUnit = Me.cboActivity.Column(4)
    Me.cboTimePeriod = Me.cboActivity.Column(3)

Notice how column numbering begins with 0.

Lines 70-71: BRAVO! Great habit to get into. This allows you to see what your SQL string looks like and even test it if you need to. This is a good practice. Great job!

Line 74: As mentioned before, cboPackageNumber was just updated. It is enabled.

Lines 90-111: You said this is an ubound form. You cannot move to a new record.

Lines 129, 133: First, you got it right in lines 70-71. Always create a string first, then act on it. Second, your first SQL statement adds a new record, assigning a value to LeadTimeHigh with the value of cboMax. Your second SQL updates the value of LeadTimeHigh to cboMax. There is no need for the second SQL to be executed, as LeadTimeHigh is already set to cboMax. Finally, exactly what is it you are doing here? Do these tables have just one field? It does not appear so, as in the second SQL statement you are trying to update based upon the ID being equal to cboMax. But you are assigning the value of LeadTimeHigh to cboMax. ???? Is LeadTimeHigh an index? Is cboMax an index? Is it another type of value? This code itself is just confusing as to what you want to update to what values.

Lines 137-140: ???????

Lines 142: You cannot save this record as this is an unbound form.

Lines 143-146: Not sure the intent here. You should use Err.[Property], as this is not a macro.

So, some additional questions: Really--what are you trying to do? Please, use small words for those of us who don't understand what your intent is: "I want to assign the values from these specific combo boxes to these tables under these specific conditions."

[AdmiralN]

Hi twinnyfo,

First of all thank you so much for the your very detailed reply. It must take you considerable time to write a response such as that and I am incredibly grateful that people like you are willing to provide help to beginners like me for free.

As for the naming convention for the combo boxes, your point is dully noted. It was not something I considered when naming the text boxes, but I accept how confusing it must be for someone else trying to understand my code.

I hadn't considered how much crap I had in the code when I posted it on here. I have mostly been experimenting with trial and error, taking pieces of code from different sources, mainly forums such as this, and seeing what the affect was (if any) on the form.

I will take some time to work through your comments one by one to understand the errors. In the interim, it seems I have done a rather a poor job of explaining what I am trying to achieve. Let me see if I can explain better below.

My initial spreadsheet had columns:- Package No., Package Description, Activity, Productivity Rate High, Productivity Rate Low, Unit, Time Period, Notes, Source, Lead Time High, Lead Time Low. - Containing several hundred records, some of which were incomplete.

Package number and Description are effectively the same. The 'Activity' column contains activities under each package -these are unique records and each has a unique 'productivity rate high', productivity rate low', 'Source', 'Notes' and 'Leadtime High and Low columns.

My current form contains combo boxes for 'Package No' and 'Activity' and then text boxes for all the other fields. Currently I can select a package no and then the second combo box will filter for the activities within that package. Once an activity is selected the various text boxes (ProductivityHigh, ProductivityLow, Unit, Time Period, Source, Notes, Lead time - Max and Min) are populated with the rest of the information for that record.

The form currently works great as a means to look up records, but not much else. I would like to be able to add new records (i.e. add new 'Activities' and the details related.) and also to be able to edit existing records.

I'm also concerned my table structure isn't optimal. I currently have tables:-

tblPackages - with cols 'Package No', 'Package Description', 'ID' (PK)

tblActivity - with cols 'Activity', 'Unit', 'Time Period', 'ID'(PK), 'RateID', 'PackageID'.

tblUnitPeriod - with cols 'ProductivityRateLow', 'ProductivityRateHigh', 'ID'(PK)

tblID - with cols 'ID'(PK), 'Notes', 'LeadtimeLow', 'LeadTimeHigh', 'Source', 'ActivityID'

Thanks again.

[AdmiralN]

Just some further updates as I'm working through your reply.

Lines 41-53 - I removed these as you correctly pointed out they were doing nothing. Originally I was trying to get the text boxes to clear once a new package number was selected. At the moment the values from the previous record remain visible until the cboActivity is selected.

Lines 65-69 -I acknowledge this code is confusing. I did swap it for the your simplified version, but I then lost the activities values in cboActivity. I also realize I could have populated some of the text boxes from cboActivity as they were present in the same table.

Line 74 -I've removed this as rightly mentioned, it does nothing.

Lines 90-111 - I removed this code for the button save. I think this was originally generated through the button wizard. But as you say would never work with an unbound form.

Lines 129, 133 -I had pasted from another forum post a piece of code I thought I could adapt to save edits to records. I was (and still am) in way over my head.

Lines 137-140 -I'm not even sure where that came from! :) Deleted.

Lines 143-146 - Again I'm confused what I was trying to achieve here. Deleted.

Also, I renamed the Text boxes with the 'txt' Prefix as you suggested.

Thanks!

[twinnyfo]

A few quick thoughts:

Your spreadsheet has the following fields (we'll call them "fields" so that we maintain nomenclature continuity with a DB structure) "Package No., Package Description, Activity, Productivity Rate High, Productivity Rate Low[/b], Unit, Time Period, Notes, Source, Lead Time High, Lead Time Low". Here is my question: why split these fields out into different tables? I can only assume that they are exported to Excel (or are simply contained in Excel) as coherent "records"? Unless there is an absolute "need" to create separate tables, it is much easier to maintain the integrity of the records as is.

The 'Activity' column contains activities under each package -these are unique records and each has a unique 'productivity rate high', productivity rate low', 'Source', 'Notes' and 'Leadtime High and Low columns.

This is quite confusing! The Activity column has multiple columns? Please explain.

Finally, if you are using Excel as your source data, I can only assume that you have had some means to import the data into a table(s)? If you use one table (as described above), the simplest approach is to have a form bound to that table. Then you can add/modify/delete records and do whatever you want. Then you can view an entire record at a time. Unlesss I am missing something significant here, this would be your approach.

Standing by for greater details....

[AdmiralN]

Hi twinnyfo,

The data was imported was imported from a single excel spreadsheet via the import wizard within access. I then used the table wizard to break the single table of records into several tables. In hindsight, as you have pointed out, this may have been a mistake. I had thought (for some reason) that is was better to have the records broken into different tables.

As for the Activity Column, what I meant was that all the activities are unique, but they may have the same package number. There is a finite list of Packages, each unique activity is assigned to a package (package number).

Hopefully I have explained a little better. Apologies for confusing the issue.

[AdmiralN]

Ok so I have 're-jigged' the form so it is bound to 1 table with all the records. This is identical to the excel spreadsheet.

I have my text boxs all bound to the cboActivity to auto-popluate via the Control Source and relevant column. The issue now is if I try to edit the data in the text boxes it says 'Control can;t be edited as it's bound to the expression '[cboActivity].[Column]().

How can I make it allow me to edit the records?

The form properties option 'Allow edits' is set to yes.

Update- I changed to column assignment to the after update event of the cboActivity. I can now edit the text box fields again, but any changes I make to the fields are not saved back to the table.

[twinnyfo]

I have my text boxs all bound to the cboActivity to auto-popluate via the Control Source and relevant column. The issue now is if I try to edit the data in the text boxes it says 'Control can;t be edited as it's bound to the expression '[cboActivity].[Column]().

I have no idea what this means.

Update- I changed to column assignment to the after update event of the cboActivity. I can now edit the text box fields again, but any changes I make to the fields are not saved back to the table.

I have no idea what this means.

Why do you have multiple combo boxes? You have one table. You combo boxes do not need to be "connected" in any way.

I am very confused on this...

[AdmiralN]

I appreciate your patience. Your response seems to indicate I'm missing something obvious here. Maybe I'm over complicating what should be a very simple solution.

Please allow try to explain what I'm trying to achieve once more.

At the moment I have one table - tblAll

This has columns titled ID (PK), PackageNo, PackageDescription, Activity, ProductivityRateLow, ProductivityRateHigh, Unit, TimePeriod, Notes, Source, LeadTimeLow, LeadTimeHigh.

My form contains combo boxes cboPackageNumber and cboActivity

And text boxes txtRateHigh, txtRateLow, txtUnit, txtTimePeriod, txtSource, txtNotes, txtMax, txtMin

I have added buttons cmdSave, cmdAdd, cmdClose

The user first selects the Package Number from cboPackageNumber, then selects the Activity from cboActivity, from a list of activities associated with that package. Once the Activity is selected the rest of the text boxes are populated with the data for that activity.

This all works great to look up records from the table. However, I would like users to be able to save changes to the records in the form back to tblAll. I also want to be able to add new records. At the moment if any information is changed in the text box fields on the form it is not saved back to the table.

My VB code is currently this:-

Option Compare Database
Private Saved As Boolean
Option Explicit
 
 
Private Sub cboActivity_AfterUpdate()
 
Me.txtRateHigh = [cboActivity].Column(5)
Me.txtRateLow = [cboActivity].Column(4)
Me.txtUnit = [cboActivity].Column(6)
Me.txtTimePeriod = [cboActivity].Column(7)
Me.txtSource = [cboActivity].Column(9)
Me.txtNotes = [cboActivity].Column(8)
Me.txtMax = [cboActivity].Column(11)
Me.txtMin = [cboActivity].Column(10)
 
 
End Sub
 
 
Private Sub cboPackageNumber_AfterUpdate()
 
Dim strSql As String
strSql = "SELECT o.ID, o.PackageNo, o.PackageDescription, o.Activity, o.ProductivityRateLow, o.ProductivityRateHigh, o.Unit, o.TimePeriod, o.Notes, o.Source, o.LeadTimeLow,  o.LeadTimeHigh " & vbCrLf & _
"FROM tblALL AS o" & vbCrLf & _
"WHERE o.PackageNo = '" & cboPackageNumber.Value & "'" & vbCrLf & _
"ORDER BY o.PackageNo;"
Debug.Print strSql
cboActivity.RowSource = strSql
 
If cboPackageNumber > 0 Then
 
    cboActivity.Visible = True
    Me.cboActivity.Requery
 
 
Else
 
    cboActivity.Enabled = False
 
End If
End Sub
 
Private Sub cmdSave_Click()
 
   Saved = True
   DoCmd.RunCommand (acCmdSaveRecord)
   Me.cmdSave.Enabled = False
   Saved = False
 
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim Response As Integer
If Saved = False Then
    Response = MsgBox("Do you want to save the changes on this record?", vbYesNo, "Save Changes?")
    If Response = vbNo Then
       Me.Undo
    End If
    Me.cmdSave.Enabled = False
End If
End Sub
 
 
 
Private Sub cmdAdd_Click()
 
On Error GoTo cmdAdd_Click_Err
 
    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
 
cmdAdd_Click_Exit:
    Exit Sub
 
cmdAdd_Click_Err:
    MsgBox Error$
    Resume cmdAdd_Click_Exit
 
End Sub
 
 
Private Sub Form_Dirty(Cancel As Integer)
 
Me.cmdSave.Enabled = True
End Sub

The code for the cmdSave I copied from another source, which seemed to be what I was trying to achieve with the save function.

Sorry to dump all my code here again, I'm hoping this makes it easier to understand where I am and where I'm going wrong.

[AdmiralN]

Hi twinnyfo,

There's something I should have explained, which is impossible to understand without seeing the actual table with the data. The reason I have 2 combo boxes and needed them to be linked is because of package numbers. The first combo box contains the list of package numbers of which there are around 30 no. This list of package numbers is fixed and will not change. Each record (Activity) is assigned to one of these package numbers.

The way I need the form to work is for the first combo box to show the list of 30 package numbers. Once a package is selected the second combo box will only show the activities assigned to that package number.

Can this be done with one form and still allows edits to the records?

Would it be better to split the package numbers and package description columns into a separate table as this list will never change?

[twinnyfo]

Yes - you need to use a bound form. Filter the form by each combo box in sequence. With a bound form, any changes to the record are saved to the table. You can move to a new record to add records. Get to the bound form first, as this is the first, most important step.

Yes, I do think you are highly over complexifying in this.

[NeoPa]

AdmiralN:
Can this be done with one form and still allows edits to the records?

I won't go into the whole thread but this stood out as a straightforward question. There are articles around that show how to do this (Cascaded Form Filtering).