Problem Submitting Data to Table from Form

[Horizon57]

I am working on GUI where a few forms will allow users to populate a table.

Essentially the user would open the db, then open the 'CurrentAnalytics' form and make a selection from the comboboxes and submit. Then they would select the HighClaims form which opens the input GUI. This where the problem occurs.

After text is entered, and the submit button is pressed, the table needs to be populated with the text and the text also needs to show up in middle grey text box when the form reloads. Currently my VBA code is not accomplishing these two objectives...

If anyone could take a look at the code behind these forms and offer some assistance I would greatly appreciate it.

I have attached the access db as well as posted my code below.

Form1 opens Form2, which opens Form3, which populates db and refreshes.

Form 1 (Current Analytics)
'Declare Variables for GroupName & GTSN
Dim GN As String
Dim GT As String


Private Sub cboGroups_AfterUpdate()

'Refresh the form data
Me.cboGTSN.Requery
End Sub



Private Sub Form_Open(Cancel As Integer)
'DoCmd.Maximize
End Sub



Public Sub SubmitGroups_Click()

'Open form AnalyticsMenu
DoCmd.OpenForm "AnalyticsMenu"

'Set vars to current GroupName and GTSN to pass to other forms
GN = Me.cboGroups
GT = Me.cboGTSN

'Close form CurrentAnalytics
DoCmd.Close acForm, "CurrentAnalytics"
End Sub


Form 2 (Analytics Menu)
Option Compare Database


Private Sub HighClaims_Click()
'Open Form HighClaims
DoCmd.OpenForm "HighClaims"
DoCmd.Close acForm, "AnalyticsMenu"
End Sub


Private Sub MainMenu_Click()
'Open Form MainMenu
DoCmd.OpenForm "CurrentAnalytics"
DoCmd.Close acForm, "AnalyticsMenu"
End Sub


Private Sub Exit_Click()
'Open Form Exit
DoCmd.Close acForm, "AnalyticsMenu"
End Sub


Form 3 (HighClaims)
Option Compare Database

'Define vars
Dim HighClaimsGN As String
Dim HighClaimsGT As String



'Define opening state
Public Sub Form_Open(Cancel As Integer)

Dim HighClaimsGN As String
Dim HighClaimsGT As String

'Query tblAnalytics to populate the EndNotesBox
HighClaimsGN.Value = CurrentAnalytics.GN.Value
HighClaimsGT.Value = CurrentAnalytics.GT.Value
EndnotesBox = DLookup("HighClaimsEndNotes", "tblAnalytics", "GRPNM = " & HighClaimsGN And "GTSN = " & HighClaimsGT)
End Sub


'Clear text in update textbox
Private Sub Clear_Click()
Me.UpdateBox.Value = Null
End Sub


'Close the current form and open the Main Menu via Exit button
Private Sub Exit_Click()
DoCmd.OpenForm "AnalyticsMenu"
DoCmd.Close acForm, "HighClaims"
End Sub


'Close the current form and open the Main Menu
Private Sub ReturnMM_Click()
DoCmd.OpenForm "AnalyticsMenu"
DoCmd.Close acForm, "HighClaims"
End Sub


'Submit analysis to tblAnalytics and refresh form including populating the grey box with the updated analysis
Private Sub UpdateBox_Click()


End Sub

 

[tokoloshi]

Submitting data to table from form

H57, the first thing I needed to solve in my head when i started working with Access is that one should not try to solve too many things just through code.

Rather get into the mindset of forms, queries, tables and reports.

If your want to change data programatically, ask yourself if you can change it through a standard query first.

If you can create a typical update/insert/delete query that does the job for you in the query designer then you know what the query needs to do.

Next ask yourself what data needs to be input by the user and what can you extract without user input. For example, you may already know the user's details so you can inputer the logonID as the person who has updated the record. You may also have certain default values available. For example the South African Identity number can be "unpacked" to provide date of birth and gender, so i do not need to collect this from the user as well - possibly the same could be true for social security numbers or other national id numbers.

Now, armed with the knowledge of what needs to be collected you can populate a form with text boxes that can collect this needed info.

You can then generate a sqlString that will replace the query with a dynamically constructed query and run this with a docmd.runsql.

There are several methods you can follow with this example, it really comes down to programming styles and preferences.

With regards to your DB, there are a couple of comments so far.

1. Set the startup form to be the form you want them to see first so that they do not need to first open the DB then open the form. This is in any event going to become problematic if you want to start delivering your solution via the run-time method, so better to get into the habit of setting the opening and closing forms from the beginning.
2. I cannot understand the purpose of the CurrentAnalytics form. I understand that this is a skeletal system for demonstration purposes, but do you need to have this form bound to a table?
3. The GroupName combo box's record source should preferably be:
   1. SELECT DISTINCT tblGroups.[GroupName], tblGroups.GTSN
      FROM tblGroups
      GROUP BY tblGroups.[GroupName], tblGroups.GTSN
      ORDER BY tblGroups.GTSN;
   2. You should also then set the bound column to one, the number of columns to 2 and the column widths to 0;4cm so that the information provided to the end user is user friendly while ensuring the DB's integrity needs are also met.
   3. You should also consider normalising your groups table so that you can avoid the distinct clause. It becomes exceptionaly difficult to guarantee referential integrity through your code when in fact that is exactly what an RDBMS is designed to do for you. That way also you will be using the first combo box to select a group from the first normalised group table, requery 2nd combo to filter out all of the relevant options and then move on.
4. The Navigation buttons and record selectors are really necessary for this form if there is no need for it to be bound to a table, but then I do not understand the business need for the form.
5. Lastly, I would suggest changing the 2nd combo box's row source to :
   1. SELECT DISTINCT tblGroups.GroupName, tblGroups.GTSN
      FROM tblGroups
      WHERE (((tblGroups.GroupName)=[Forms]![CurrentAnalytics]![GTSN]))
      ORDER BY tblGroups.GTSN;
   2. Furthermore, even this will be refined once you have properly normalised your DB. The point here is to point your criteria at the current form, not at just a value. Use the builder wizard for this - it is faster.

Hope this gets you started.

Post an updated DB once you done some work on this and I will gladly take another look.

Sean