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?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Need help with problem on appending data to table
- Thread starter tmcrouse
- Start date
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.
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.
- Thread starter
- #4
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:
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?
Code:
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 IfCould 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?
- Thread starter
- #6
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.
- Thread starter
- #7
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.
[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.
- Thread starter
- #9
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.
- Status
Similar threads
- Locked
- Question
