Need help with problem on appending data to table

[tmcrouse]

I have a large Access db and created all my tables that are normalized, created forms, reports, and queries. I used fake test data that consisted of 14 rows. Everything looked great until I appended REAL data that overwrote the 14 rows and added 50k more rows. The forms are not picking up this new data. If I manually enter each line item the forms pick up that information. I cannot manually enter 50k rows of data. Does anyone know how to get around this? Do I need to just hit submit 50k times so I get 50k lines and then do an update query?

 

[MajP]

You will need to describe this much clearer, it does not make any sense. I interpret this to mean you ran an append query that added 50k of real records to your tables. I assume when you did this you also appended all the foreign keys. Now however you do not see the data in the form.

My guess is that in your data table you did not append the foreign keys and your form is based on a query with an inner join

where somefield = someotherfield

since you did not update the foreign keys no records are returned by your query therefore you do not see any records in your form. Please explain in detail how you appended the 50k records. Sounds to me like you will either have to run an update query to update the FKs or reappend the data. But that is a guess.

 

[MajP]

You will probably need to explain the tables in question and the pertinent primary and foreign keys.

 

[tmcrouse]

Now I have 1.3 million records appended, due to time constraints and when I use my search form the listbox automatically begins at row 46. I used rows 1-46 prior to my append of the real data for testing purpose. I made up stuff. Then I copy and pasted 1-46 rows from the real data to overwrite the fake stuff. Then appended the rest. I had 9 spreadsheets to work with data and my first was 50k rows. So, now all the data is in my main table and I can see records now, but they start with row 46 and do not include all 1.3 million. I can see only 286k of them in this listbox. Not all rows have complete data. Some fields are null. So, I am thinking that has something to do with it. My PK is autonumber and I thought I could code in PK not null, but that did not work. I want all my 18 columns to show up in this listbox regardless of being complete. Here is the code behind my search key:

Option Compare Database

Option Explicit

Global GlobalID As Long 'used to hold an ID for a short time (after a record is added)

 

Public Sub sSortListBox(anyListbox As Control, Button As Integer, Shift As Integer, X As Single)

 

Dim strSql As String

Dim vGetWidths As Variant

Dim vArWidths() As Variant

Dim iColCount As Integer, iColNumber As Integer

Dim iLoop As Integer

Dim iColWidthSum As Integer

Dim iUndefined As Integer

Dim iDefaultWidth As Integer

Dim strOrderBy As String

Const strListSeparator As String = ";" 'list Separator

'If your list separator is not a ";"

'you will need to change the ";" to your list separator

 

    On Error GoTo ERROR_sSortListBox

 

    If Button <> acRightButton Then

       'only sort based on right button being clicked

 

    ElseIf anyListbox.RowSourceType <> "table/query" Then

       'only sort listbox based on queries

       msgbox "List box must use a query as it's row source"

 

    ElseIf Len(anyListbox.RowSource) = 0 Then

       'Nothing there, so ignore the click

 

    ElseIf Not (InStr(1, Trim(anyListbox.RowSource), "Select", vbTextCompare) = 1 _

       Or InStr(1, Trim(anyListbox.RowSource), "Parameters", vbTextCompare) = 1) Then

       'If rowsource does not start with SELECT or PARAMETERS then

       'assume it is a table not a query

       msgbox "List box must use a query as its row source"

 

    ElseIf anyListbox.ColumnCount <> DBEngine(0)(0).CreateQueryDef("", anyListbox.RowSource).Fields.Count Then

       'Column count must be correctly set, otherwise this routine

       'could cause errors. Column count set less than actual field count

       'will cause subscript errors.  Column count set higher than actual

       'field count can cause listbox to display nothing if "Extra" column

       'is clicked.

       msgbox "List box column count does not match query field count!"

 

    Else   'passed the error checks

 

       With anyListbox

          'Column count must be correctly set, otherwise this routine

          'could cause errors. Column count set less than actual column Count

          'will cause subscript errors.  Column count set higher than actual

          'column count can cause listbox to display nothing if "Extra" Column

          'is clicked.

          iColCount = .ColumnCount

          ReDim vArWidths(iColCount - 1, 0 To 1)

 

          vGetWidths = Split(.ColumnWidths, strListSeparator, -1, vbTextCompare)

 

          'Assign values to array that holds length and running sum of Length

          For iLoop = 0 To UBound(vGetWidths)

             iColWidthSum = iColWidthSum + Val(vGetWidths(iLoop))

             vArWidths(iLoop, 1) = iColWidthSum

             vArWidths(iLoop, 0) = vGetWidths(iLoop)

          Next iLoop

 

          'Adjust any colwidths that are unspecified:

          'The minimum is the larger of 1440

          'or the remaining available width of the list box

          'divided by number of columns

          'with unspecified lengths.

          For iLoop = 0 To iColCount - 1

             If Len(vArWidths(iLoop, 0) & vbNullString) = 0 Then

                iUndefined = iUndefined + 1

             End If

          Next iLoop

 

          If iUndefined <> 0 Then

             iDefaultWidth = (.Width - iColWidthSum) / iUndefined

          End If

 

          If iDefaultWidth > 0 And iDefaultWidth < 1440 Then

             msgbox "Sorry! Can't process listboxes with horizontal ScrollBars "

             Exit Sub  'Horizontal scroll bar present

          Else

             'recalculate widths and running sum of column widths

             iColWidthSum = 0

             For iLoop = 0 To iColCount - 1

                If Len(vArWidths(iLoop, 0) & vbNullString) = 0 Then

                   vArWidths(iLoop, 0) = iDefaultWidth

                End If

                iColWidthSum = iColWidthSum + Val(vArWidths(iLoop, 0))

                vArWidths(iLoop, 1) = iColWidthSum

 

             Next iLoop

          End If

 

          'Set right edge of last column equal to width of listbox

          vArWidths(iColCount - 1, 1) = .Width

 

          'Determine which column was clicked

          For iLoop = 0 To iColCount - 1

             'If X - .Left <= vArWidths(iLoop, 1) Then

             If X <= vArWidths(iLoop, 1) Then

                iColNumber = iLoop

                Exit For

             End If

          Next iLoop

          iColNumber = iColNumber + 1   'adjust since iLoop is 0 to n-1

 

          'rebuild sql statement

          If iColNumber > 0 And iColNumber <= iColCount Then

             strSql = Trim(.RowSource)

 

             If Right(strSql, 1) = ";" Then strSql = Left(strSql, Len(strSql) - 1)

 

             iLoop = InStr(1, strSql, "Order by", vbTextCompare)

             If iLoop > 0 Then

                strOrderBy = Trim(Mid(strSql, iLoop + Len("Order by")))

                strSql = Trim(Left(strSql, iLoop - 1))

             End If

 

             'Build the appropriate ORDER BY clause

             If Shift = acShiftMask Then

             'If shift key is down force sort to desc on selected column

                strOrderBy = " Order By " & iColNumber & " Desc"

 

             ElseIf Len(strOrderBy) = 0 Then

             'If no prior sort then sort this column ascending

                strOrderBy = " Order by " & iColNumber & " Asc"

 

             ElseIf InStr(1, strOrderBy, iColNumber & " Asc", vbTextCompare) > 0 Then

             'If already sorted asc on this column then sort descending

                strOrderBy = " Order By " & iColNumber & " Desc"

 

             ElseIf InStr(1, strOrderBy, iColNumber & " Desc", vbTextCompare) > 0 Then

             'If already sorted desc on this column then sort Ascending

                strOrderBy = " Order By " & iColNumber & " Asc"

 

             Else

                 strOrderBy = " Order by " & iColNumber & " Asc"

             End If

 

             strSql = strSql & strOrderBy

             .RowSource = strSql

 

          End If   'Rebuild SQL if col number is in range 1 to number of columns

       End With 'current list

    End If  'Passed error checks

 

EXIT_sSortListBox:

    Exit Sub

 

ERROR_sSortListBox:

    Select Case Err.Number

       Case 9  'Subscript out of range

          msgbox Err.Number & ": " & Err.Description & _

                 vbCrLf & vbCrLf & "Check column count property of list box.", _

                 vbInformation, "ERROR: sSortListBox"

 

       Case Else 'unexpected error

          msgbox Err.Number & ": " & Err.Description, vbInformation, _

                 "ERROR: sSortListBox"

    End Select

 

    Resume EXIT_sSortListBox

End Sub

 

Public Function BuildFilteredSQL(filtertext As String, filterType As String, sqltext As String) As String

On Error GoTo Err_BuildFilteredSQL

'Builds a sql statement using the filtertext, filtertype and sqltext

'The filtertext is the string to filter on

'The filterType determines what fields are used to do the filtering on

'Note: these are hardcoded in this routine

'the sqltext determines the base sql query to modify

 

    Dim sql As String

    Dim fff As Integer

    Dim sqlwhere As String

    Dim doneFlag As Integer

        

    sql = sqltext

    'good for debugging

    'MsgBox sql & "||"

    

    'take off extra spaces and ";" if they exist

    sql = RTrim(sql)

    If (Right(sql, 1) = ";") Then

        sql = Left(sql, Len(sql) - 1)

    End If

    

    'Build a new WHERE clause based on passed in parameters

    If (filtertext <> "") And (filterType <> "") Then

        Select Case filterType

            Case "Quality"

                sqlwhere = "WHERE ((MEASURES.HEDIS_MEASURE Like '*" & filtertext & "*') OR (PROGRAM.Prog_NM Like '*" & filtertext & "*') OR (CONTACTS.ContacT Like '*" & filtertext & "*') OR (COMMUNICATION.Comm_type Like '*" & filtertext & "*') OR (BUS_UNIT.bus_unit Like '*" & filtertext & "*') OR (LOB.lob Like '*" & filtertext & "*') OR (PRODUCT.prod_nm Like '*" & filtertext & "*') OR (FREQUENCY.freq Like '*" & filtertext & "*') OR (COMM_LVL.comm_lvl Like '*" & filtertext & "*')OR (STATE.st_cd Like '*" & filtertext & "*')OR (COMM_LVL.comm_lvl Like '*" & filtertext & "*') OR (MEASURES.sub_measure Like '*" & filtertext & "*')OR (YEAR_TABLE.yr Like '*" & filtertext & "*')OR (MONTH_TABLE.mth Like '*" & filtertext & "*'))"

           Case "Resource"

                sqlwhere = "WHERE ((tblResource.hedis_measure Like '*" & filtertext & "*') OR (tblResource.prog_nm Like '*" & filtertext & "*') OR (tblResource.contacT Like '*" & filtertext & "*') OR (tblResource.comm_type Like '*" & filtertext & "*') OR (tblResource.bus_unit Like '*" & filtertext & "*') OR (tblResource.lob Like '*" & filtertext & "*') OR (tblResource.prod_nm Like '*" & filtertext & "*') OR (tblResource.freq Like '*" & filtertext & "*') OR (tblResource.comm_lvl Like '*" & filtertext & "*') OR (tblResource.st_cd Like '*" & filtertext & "*') OR (tblResource.comm_lvl Like '*" & filtertext & "*')OR (tblResource.sub_measure Like '*" & filtertext & "*')OR (tblResource.yr Like '*" & filtertext & "*')OR (tblResource.mth Like '*" & filtertext & "*'))"

        End Select

    End If

Could it be that since I used test data and now overwrote those 46 lines and did not move everything into a brand new DB it just thinks I am using old stuff. Everything is inter-connected and it is just stuck that way?

 

[PHV]

Why do you use so complex listbox instead of a simple subform in DataSheet view ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tmcrouse]

Actually, I did not code this. Someone else did and after I made several attempts they said let me see your DB. I gave them it and this was what they came back with due to the intricacy of the database and forms. This is not a simple data entry or data update group of forms. It is a very complex DB in which why we are doing it in Acces is beyond me. I told them when I was hired it should be SQL Server with Visual Studio because we have to host it without webserver or SAS EG or SAS AF. Both of which I know how to code. Access/VBA I have not done since college in 2002 and have had all sorts of issues with it. So, I had to reach out for help from many people and this code happened to be the help of someone either her or maybe it was the Access forum. Don't remember. But, this was their solution and now we have no time to redo the entire thing since getting ready to go live due to deadlines. Personally I have been working from 4am to 10pm nightly on this project and even over the weekend. Spent fathers' day working 7 hours and my husband was not entirely thrilled.

 

[tmcrouse]

How do I post this is solved?

 

[Andrzejek]

If you find any post helpful, mark it with the star:
[blue]
Like this post?
Star it![/blue]

If YOU are the one who figure out your own solution, provide it here so others may benefit from it.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[tmcrouse]

The solution was that I did not have a right join and this is why all my records did not show up. I thought I had to do something different. Restart the count or rebuild the table and all the while it was not showing all the data because some fields had null values. When I changed my SQL to a right join all the rows were there.