Loop through a table vba Results

Hello, first time posting with the site, so I hope I get this right.

I am building a form for entering data into a single table. I referred to another user's VBA code to check for multiple blank fields in the form by using the Other Tag property for the fields I want populated and then set the focus back to the first field in the form if there were any blanks.

The idea is to require the user entering data to enter data into these required fields. The code works, mostly, because it stops the record from being entered the first time thorugh. The problemm is that if the user tabs through or clicks the arrow to move to a new record on the form without changing or adding any data the second time, when the ok is clicked it adds the record to the table with the fields that I want populated as blank. I am using the following code on the Forms BeforeUpdate Event.

I would like to use this generalized code so that I can easily incorporate it into other forms that I will be designing in this same database.

Thanks in advance for any help you can offer!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String
'Loop through every control on the form
For Each ctr In Me.Controls
'Look for a Particular Tag
If ctr.Tag = "BlkChk" Then
'Create a List of empty questions
If IsNull(ctr) Then
strMsg = strMsg & "_ " & ctr.Name & vbCrLf
End If
End If
Next ctr
'Did We Find Any Unanswered Questions?
If strMsg "" Then
If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
vbOKOnly) Then
End If
End If
End Sub

I haven't had a lot of exposure to Access and VBA for around ten years, so I'm practically a newbie. I remember a little bit of VBA, and I know some powershell and vbscript. I apologize in advance for my ignorance and stumbling.

I need to print a series of weekly reports for different departments. I don't want to maintain a different report for each department, so I'd like to be able to change the report header and query source for one report using VBA.

Here's how I was thinking of doing it:
Get the name of the first department from a table. Get the appropriate query name from the department name. They are all named "qryDepartment". Open the report (rptWeekly) in design mode. Change the report record source to qryDepartment. Change the report header (Label26) to "Department Weekly Report". Export the report to PDF. Go back to #1 for the next dept. Except I can't remember how to do most of this. I can export the report to PDF, but how do I loop through the department names table, and how do I change the record source and header before exporting it?

If there's a better way to do this, I'm open to that too.

Thank you!


I am new to VBA and I need to create a button that will read from two tables and append to an existing record in a table. Here is the scenario.

I have an Access database that has two tables called “Hardwaretable” and “AMSInfo”. There are about 6,000 records in the Hardwaretable and 14,000 records in the AMSInfo table. Both tables have the same field names, Serial_Number, Model_Number, Bar_Code, PO, Cost, Invoice, Inv_Date, Acq_Date and Source. I need the program to look at two fields in each table, the “Serial_Number” and the “Model_Number”. If the records within “Serial_Number” and “Model_Number” match then take the records from fields “Bar_Code”, “PO”, “Cost”, “Invoice”, “Inv_Date”, Acq_Date” and “Source” from the “AMSInfo” table and append them to the same fields in the “Hardwaretable”. I wrote the following code, but every time it starts running it stops halfway through the “AMSInfo” table. I set up a simple loop that goes through and JUST READS from “AMSInfo” table but it always stops in the same place. Am I not doing something wrong? Please help. Here is my code..

Private Sub cmdPush_Click()
'data goes from AMSInfo to Hardwaretable
Dim rstht As DAO.Recordset 'Hardwaretable
Dim rstams As DAO.Recordset 'AMSInfo table
Dim serialnum
Dim modelnum
'open first table and go to first record
Set rstht = CurrentDb.OpenRecordset("Hardwaretable")

'open second table and go to first record
Set rstams = CurrentDb.OpenRecordset("AMSInfo")

'each time it grabs a serial number and model number from table1, it loops through
' the entirety or table2.
Do Until rstams.EOF
ActiveXCtl2.Value = rstams.PercentPosition
serialnum = rstams.Fields("Serial_Number").Value
modelnum = rstams.Fields("Model_Number").Value
Debug.Print serialnum & " : " & modelnum
'here is where it loops through all of table2 looking for a match to serialnum and modelnum
Do Until rstht.EOF
If ((rstht.Fields("Serial_Number").Value = serialnum) And (rstht.Fields("Model_Number").Value = modelnum)) Then
'put any writing within the "edit/update" block
rstht.Fields("Bar_Code").Value = rstams.Fields("Bar_Code").Value
rstht.Fields("Manufacturer").Value = rstams.Fields("Manufacturer").Value
rstht.Fields("Cost").Value = rstams.Fields("Cost").Value
rstht.Fields("PO").Value = rstams.Fields("PO").Value
rstht.Fields("Inv_Date").Value = rstams.Fields("Inv_Date").Value
rstht.Fields("Invoice").Value = rstams.Fields("Invoice").Value
rstht.Fields("Source").Value = rstams.Fields("Source").Value


End If
'move to the next record in hardwaretable
'since it is at the end of table2, AMSInfo, it needs to be reset to first record
'move to next record in table1, Hardwaretable
'close both tables now

Much appreciated

Hello everyone

Looking for some help. We have a table that describes input/values that the user will select. The user will input the possible values, then they will be coded in XML based on VBA.

I'm in need of some help in coding this on running a loop to check all of the fields to
1) See if there is a value in the field
2) If so, is it an element, or an attribute of an element
3) Have it write the XML tag

I'm very new to this, however I have seen through searching a lot of decoding from XML to VBA, but nothing about converting TO VBA using loops and going through an entire table of possible IF conditions.
Below is an example of the table - as said, some have values - others do not and should be ignored. Any help is appreciated!

example of the table: h.ttp://i.imgur.com/a7yBuNX.jpg

Basically, what I have is a PC inventory database that has something called a 'swap' feature. Swap takes a PC currently out in the field and swaps basic user and location information with a brand new PC that we just got in.

One of the records we keep is a list of software on the PC. This is stored in a table with one line for each piece of software for each PC (thus, multiple lines for the same PC and multiple lines for the same software on different PCs, this is how it was set up when I arrived).

I have one report where the software is a subreport. Is there a way I can dynamically figure out the number of records on this software subform using VBA so that I can loop through them and update what computer each software has during a swap?

Hi there, who fancies helping the new guy?

I'm having a bit of a problem with Groupwise automation. I have a need to email a list of people and attach a different file for each individual. e.g

John Smith gets File1.xls
Tom Jones gets File2.xls etc, etc...

The recipient's name and email address are held in an Access table, tblMailList, in fields 'Recipient' & 'Mail' respectively. The attachments are created by a seperate routine and named by appending the recipient name to a standard stem, e.g

Filename = Path & "File " & [Recipient] & ".xls"

I've simplified things a bit for clarity.

Basically I want to loop through tblMailList, creating a new mail item for each value of [Recipient], attaching 'Filename' and sending to [Mail]. I'll also need to add body text and subject lines which incorporate string variables. I can handle everything except the Groupwise bit.

I'm using A97 on NT4 with GW5.

I'm an intermediate VBA user, but a novice with Groupwise so any help would be greatly appreciated.

Thanks, Iain

Hi Group,
I've used a import specification to import a Fixed-width file. However because of the layout of the initial file, i.e the fields' header starts BEFORE the fields data, I need to do some manipulation to get rid of the extra space in each row.

How can I loop through each record of the table to get rid of this extra space. Is there a a special character I can use for a space?

Kind Regards

I have some VBA which places values from a list box into a single field in tblTracking, seperated by commas:


How can I seperate these values after pulling them out again into an array, so I can loop through that and highlight the appropriate selections in the list box?

Hi all, wonder if you can help out with the code to update table data.

I have a table with multipule columns, what I have to do is go through each Field row by row edit the data based on the value inside. I have a rough idea of pieces of code that I have to use to achieve this, I just do not know how to piece them toghether.

I have no clue what I am doing, this is my attempt at trying to figure this out.

Dim db As DAO.Database
Dim rstCat As ADOB.Recordset 'Make Field in tableDevice

Set db = CurrentDb

rstCat.Open "Select [Make] FROM tblDevice"
Do Until rstCat.EOF
If rstCat Like "*" & "DELL" & "*" Then
Else If
rstCat Like "*" & "IBM "*" Then
rstCat = "IBM"
End If

End Sub

Is there a way in VBA to create a DAO recordset based on a table and delete certain records only from the copy and not the actual table?

I want to use a DAO Recordset copy of a table to
1) Loop through the records and delete / remove the ones I don’t want
2) Use the remaining records to as the record source of a form.

I’ve tried .Clone and that doesn’t work. Is this possible with DAO?

I normally would use a query, but that won’t work in this instance. I’ve tried using .Filter on the forms recordsource directly but that won’t work since I can’t simply specify the criteria.

Maybe I am trying to make it harder than it needs to be. I am attempting to see if I can filter a table based on a bitmask field and display only certain records. Might also need to use the filtered records in a query. Need to see if that is possible before I attempt to use the bitmask field. I can go ahead and use several Boolean fields but could get tedious. Here’s a link to an article on bitmasking. As far as I can tell, you can only do bitwise comparisons in code which is why a query won't work (at least I couldn't get it to work).

I posted a similiar post in the 'Forms' area and I hope I'm not 'cross-posting' but I just saw this area and thought the post would be just as relevant.

I have a form that has a row of controls (checkboxes, textboxes, comboboxes, etc) for every 'driver' in a table (SalesDrivers).

I'm actually a VB Programmer messing with Access for the first time. It doesn't look like you can do Control Arrays with VBA.

I'm looking for a way to loop through the drivers in the drivers table and for each driver create a row of controls.

Is this possible with VBA?


I am trying to copy a memo field from one table to another using a SQL statement in MS Acess 2003 SP1. I have a recordset containing the original table with a memo field called "OldReportRTF". All I want to do is copy this field to another table. (This is a smplified version of what I am doing). So, while looping through the recordset, I generated the following statement:

strQuery = "INSERT INTO tblReports " & _
"[NewReportRTF], " & _
") " & _
"VALUES " & _
"'" & .Fields("OldReportRTF") & "' " & _

DoCmd.RunSQL strQuery

When I run the RunSQL command, I get the following error:
Malformed GUID, in query expression '{rtf1ansi... etc...

Both fields are memo fields, set up exactly the same way. The SQL command ran fine until I added in the memo field.

Any help would be greatly appreciated.

I have been tasked with coming up with a way to automate reconciliation of fish-ticket data in two databases. One database is administered by WA State Dept. Fish & Wildlife (no ability to access this myself) and the other by my employer (Lummi Nation). Both databases should contain identical information (yeah right) since each organisation gets duplicate copies of fish tickets produced by wholesale buyers whenever they buy fish/shellfish.

I can get a summary table in excel from both databases. I have imported them into access as tables, and done select queries to sort the records in ascending order by fish "ticket number" and then by date, species, weight landed, etc.

Each fish ticket number may have more than on record in the table (e.g., a fisherman might catch 200lbs of Chinook and 100 lbs of Coho salmon and both are reported on the same fish ticket.

So, there are three problems I need to be looking for in each table: 1-tickets that are recorded in one db but not the other, 2-records from a ticket that have no matching record in the other database (eg., 4 records in the tribal db and 5 records in the state db for the same ticket number), and 3-when parallel records do exist, are there any significant discrepancies in the recorded amounts of some of the fields for that record?

The tables are quite large (~14,500 records for one year of data) and need to be reconciled reasonably frequently during fisheries to avoid going over quota. The scope and time-consuming nature of this task if done manually has meant that, in practise, no reconciliation has been attempted in 4 years!

I have had a crack at using vba to do this task but without much success. One approach I tried weemed to be working on some sample tables (~300 records) but at about halfway through the process it kept coming up with a no current record error that I couldn't track down. I think it was a result of too much recursive activity between the functions.

I redesigned my vba approach to try to do more with do loops rather than calling functions but now I'm stuck in a loop and was wondering if someone might help me spot my problem?

What I am trying to do with this script is:
make 2 recordsets based on the ordered queries of the state and tribal tables
start at the beginning of each recordset
check the current ticket number against a table of unpaired ticket numbers (built from queries beforehand)
if they do not appear then we can exclude error type 1 and compare the actual ticket numbers.
if the numbers do not match, then we likely have an error of type 2. The record with the lower ticket number has to be exported to a table, and we move to the next record in that recordset and restart the pairwise comparison of ticket numbers.
If the ticket numbers do match, then we compare the data in each field for a type 3 error. If all is the same then we move on to the next record in each recordset and start over. If not, then we export the disagreeing records to suitable tables, and move to the next record in each recordset and start the comparison over again.
If we come to the end of a recordset, then the remaining records in the other trecordset (if any) are to be exported to the appropriate table.

I've attached a copy of the db. The code is linked to a command button on a form that should open at startup. The db is in Access 2000 format. I've tried to comment the code to explain what I'm trying to do.

So, after that long-winded explanation, I'd greatly appreciate any help that may be offered. I'm relatively new to vba and doubtless am making some error(s) that I just can't see.

After having googles I've knocked together ther following code. What I
am trying to do is select all items in one table and move through them
adding them into other tables (splitting them and putting different
parts in different table linked together with relationships).

Basicaly I have to import a 1000 rows from an excel sheet into the program but split them so that some rows are in tbl_Clients and some rows are in tbl_Contacts

I've inported the data into tbl_import and was hoping to loop through that table inserting as I go.

THe bits that I can't figure out are:
1) how to get info from the recordset (i.e. select info from that to
put into the other tables using insertSQL)

2) how to solve the Error: 91 "Object or value with block level not set" I get on Line (Set WorkRS1 =

I'm more of a php man so the code to me would be:

while($Var = mysql_fetch_array($record_set)) {
$sql = "INSERT INTO table ([Field]) VALUE (" &
$Var["FieldNameFromRecordSet"] & ")"


One final thing...does VBA have equivalent of php's mysql_inset_id()? so I can link these tables together using ClientID

Private Sub Command1_Click()

Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim strSQL As String
Dim insertSQL As String

strSQL = "SELECT ContactFirst,ContactSurname FROM tbl_Contacts ORDER BY

Set WorkRS1 = WorkBase.OpenRecordset(strSQL)

Do Until WorkRS1.EOF

insertSQL = "INSERT INTO tbl_Clients
VALUES ('','','','','','','');"




End Sub

Hi there, 6 months ago i never touched access, now im using it and getting in deeper its becoming a challenge and access is winning at the moment.
I have read through alot of these threads that are very interesting and incorparated many of my findings in my database. however i'm stuck

What i want to do is from a multiple select list box is be able to select people from a table " Members" and display as well as have them added via double click or a command button to a subform on my main form. These people are the reciepients of documents that are relevant to the each record. It is a historical log of people that this information has been given to.

I had some code happening but since wrestling with it have lost what functionality i had. Can this be done with macros or is code the most viable option,

If this means anything to anyone, your advise and suggestions would be invaluable. I am going to learn this VBA stuff, any recommended easy reading books would also be much appreciated.
thanks in advance

Private Sub testmultiselect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer

iCount = 0

If Me!NamesList.ItemsSelected.Count 0 Then
For Each oItem In Me!NamesList.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!MySelections.Value = sTemp
End Sub

Private Sub clrList_Click()
Call clearListBox
Me!MySelections.Value = Null
End Sub

Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
Dim sTemp As String
Dim sValue As String
Dim sChar As String
Dim iCount As Integer
Dim iListItemsCount As Integer

sTemp = Nz(Me!MySelections.Value, " ")
iListItemsCount = 0
bFound = False
iCount = 0

Call clearListBox

For iCount = 1 To Len(sTemp) + 1
sChar = Mid(sTemp, iCount, 1)
If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
bFound = False
If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount )), Trim(sValue)) = 0 Then
Me!NamesList.Selected(iListItemsCount) = True
bFound = True
End If
iListItemsCount = iListItemsCount + 1
Loop Until bFound = True Or iListItemsCount = Me!NamesList.ListCount
sValue = ""
sValue = sValue & sChar
End If
Next iCount
End Sub

Private Sub Send_to_RX_table_Click()
On Error GoTo Err_Send_to_RX_table_Click

DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_Send_to_RX_table_Click

End Sub

I am trying to build a function that allow me to compare dates and based on the dates relevance to current date. I have a table that has several fields of dates and I wanted to be able to color code them accordingly.

My questions are I want to be able to do this in a form or series of subforms that will query the database for all the dates and in the table and then run a function that will determine the color of the font needed for that date.
Would this be possible to set up a form to do this task.

After researching a little bit I came up with this general code:

Public Sub CompareDate(chkDate As Date)

Dim TodayDate
Dim DateDiffer
Dim lngRed As Long, lngYellow As Long, lngGreen As Long

lngRed = RGB(255, 0, 0)
lngYellow = RGB(255, 255, 0)
lngGreen = RGB(0, 255, 0)

TodayDate = DateValue(Now()) ' sets current date in MM/DD/YYYY format

' gives the difference in terms of days
DateDiffer = DateDiff(DateInterval.Day, Now, chkDate)

If DateDiffer = 0 Then
' code to make the font color = yellow
' Me!SomeFieldName.ForeColor = lngYellow
ElseIf DateDiffer > 0 Then
' code to make the font color = red
'Me!SomeFieldName.ForeColor = lngRed
ElseIf DateDiffer < 0 Then
' code to make the font color = green
'Me!SomeFieldName.ForeColor = lngGreen
End Sub

The intention here is the insert this into a module and then call this function the repeatly run through all the date fields and edit the colors each time the query is run.
How would I go about approaching the task of setting up a loop interate through a field?

Thanks for your help in advance.

Hi, I am currently installing a new network for a client and they have a custom written timesheet application running in Access. The application runs from one of the old PC's and I am trying to move it to the new server, as soon as you try to run it it stops saying you must locate the datatables which I have copied to the server.

I opened a new database then imported everything in to bypass the autoexec macro and found the following module:

Option Compare Database
Option Explicit

'default tables file location- "Office" node on net neighbourhood
Const strTablesFilepath = "officedatabasetimesheets2000v1Tables.mdb "

Function AreTablesAttached() As Boolean
' Update connection information in attached tables.

Dim strFileName As String

Dim tdf As TableDef
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb

AreTablesAttached = True

' Continue if attachments are broken.
On Error Resume Next

' Open attached table to see if connection information is correct.
Set rst = db.OpenRecordset("TimeSheetData")

' Exit if connection information is correct.
If Err.Number = 0 Then
Exit Function
strFileName = strTablesFilepath
If Not Dir(strFileName) Then
MsgBox "You Must Locate the Data Tables"

DoCmd.OpenForm "frmGetTables", WindowMode:=acHidden

Forms!frmGetTables!dlgCommon.DialogTitle = "Please Locate Data File"

strFileName = Forms!frmGetTables!dlgCommon.FileName
End If
End If

If strFileName = "" Then
GoTo Exit_Failed ' User pressed Cancel.
End If

' Loop through all tables, reattaching those with nonzero-length Connect strings.
For Each tdf In db.TableDefs
If Len(tdf.Connect) Then
tdf.Connect = ";DATABASE=" & strFileName
Err.Number = 0
If Err.Number 0 Then

MsgBox Err.Description, , "Problem with linked Tables, check location or network connection"

AreTablesAttached = False
Exit Function
End If
End If
Next tdf
Exit Function

MsgBox "You can't run this program until " & _
"you locate Data Tables", , "Tables Connection Failed"
AreTablesAttached = False

End Function

I changed the line Const strTablesFilepath = "officedatabasetimesheets2000v1Tables.mdb "
Const strTablesFilepath = " db"

But it still asks for the tables to be loaded, I'm new to access although I have used VBA a bit in Excel

Can anyone tell me what I'm doing wrong


Hi all,

I am trying to perform the find/replace function using VBA that i would do when i open a table. so when i open a table in regular view i can go to the top menu and select Edit | Replace and then i get a dialog box that i can either search the whole table or just one field and other options.... i would like to this in VBA. instead of using ODBC and looping through all records i would like to use the replace feature (not the standard replace function) with out opening up the table as a recordset. is this possible. i looked around on the internet and i saw that this was already asked a while ago, but the answer was not positive. please help me out.



here is the link (i know that Douglas steele is a great, but i am hoping for a miracle)

i have the following code which will basically has the user enter a start and end date, then runs a query using the start date as a criteria, then adds 1 to the start date and does it again, etc., until the end date is reached.

Function Float_Macro()
On Error GoTo Float_Macro_Err
Dim dateStartDate As Date
Dim dateEndDate As Date

DoCmd.SetWarnings False 'Turn off warnings
DoCmd.OpenQuery "GET Float Check Delete", acViewNormal, acEdit 'Clear Floats Table
dateStartDate = InputBox("Enter Sample Start Date", "Start Date", Date) 'User Input Box: Start Date
dateEndDate = InputBox("Enter Sample End Date", "End Date", dateStartDate + 365) 'User Input Box: End Date
Do While dateStartDate < dateEndDate 'Sets cutoff point for loop
SendKeys dateStartDate, False 'Puts user entered data for Start Date into Query Input Box
SendKeys "{ENTER}", False 'Chooses "OK" on Query Input Box
DoCmd.OpenQuery "GET Float Check Append", acViewNormal, acEdit 'adds data to list
dateStartDate = dateStartDate + 1 'Sets next sample date

DoCmd.OpenQuery "GET Float Time Card Entries Table", acViewNormal, acEdit 'Averages Float Data

Exit Function

MsgBox Error$
Resume Float_Macro_Exit

End Function

The problem is the only way I've been able to make it work is the above, where a dialog pops up initally where the user enters start date, then another where they enter end date, then each time the query is run which has the report date as a user entered field, and i use the very inefficient SendKeys to populate the field when prompted and continue on.

My desired end result would be to have the user enter a start and end date, then run the query for every date including and between, appending each query result set to the previous in a table, then display the final table.

How can i make it step through and run the query using the input box date as a starting point but then not popping up the dialog box anymore?

Hi all,

I am new to VBA and hope you can assist.

I have a database with,
1. Around 50 - 1000 tables of the same data structure.
2. Each week the number of tables differ.
3. All the tables start with CustAcctXXX where XXX is the number eg CustAcct123, CustAcct456 and so on...

I need to append all the records in these tables into a MAIN Table. No PK is needed. Creating the append queries is very time consuming and not effective because the number of tables differ each week.

Is there a way to loop through the database for these tables and append the records into the MAIN table using VBA?

Many thanks for your help in advance.


Not finding an answer? Try a Google search.