Can't access current record on form 1

[diddydustin]

Hello everyone. I'm new to this forum.

I'm trying to access the current field on the current record of a form in VBA. I've tried using Me!FieldName and Forms!FormName!FieldName. Every time it only accesses the first record on the form, never the current selected record. The form is basically a front end display for a table, but there is a calender so the user can filter out certain dates. Here is my a segment of my code:

' Set the file handle
Set conn = New ADODB.Connection

' Get the database name.
db_file = "C:\Documents and Settings\Dustin Lyons\Desktop\Network\Driver History.mdb"

' Open a connection.
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open

Dim rsMyTable As Recordset

Set rsMyTable = New ADODB.Recordset
rsMyTable.ActiveConnection = conn
rsMyTable.Open "Tispatch_Miles", , adOpenKeyset, adLockOptimistic, _
adCmdTable

' Find miles by state
Set rs = conn.Execute("SELECT * FROM T:Miles WHERE Route = '" & Forms!Dispatch_Console!Route & "'&quot

rsMyTable.AddNew

**** here is what it doesn't access the current selected record on the form, but only the first record on the form. I want to access the "Driver" field for the current selected record on the form *****
rsMyTable.Fields("Driver&quot.Value = Forms!Dispatch_Console!Driver

*****

rsMyTable.Update

For i = 1 To 15

rsMyTable.Fields(i) = rs.Fields(i).Value + rsMyTable.Fields(i).Value
rsMyTable.Update

Next

rsMyTable.Close
rs.Close
conn.Close

Any info on how to access a field for the current selected record on a form is appreciated. This has been driving me nuts! Thank you!

Dustin

 

[BlackKnight]

Hi,

You seem to be trying to do more than just retrieve the current record but here is how you can refer to the fields of the current record on a form assuming they are included in the form's recordsource.

With Forms!Dispatch_Console.Recordset
MsgBox !Driver, , "Current record on form"
End With

Have a good one!
BK

 

[diddydustin]

BlackKnight,

Thank you for the help, but it still remains to always target the first record as the current record. This code runs on a Change event of a combo box. This is killing me. Any reason why this would be doing this? Anything I can try? The form has a calender control, and clicking on the dates of the calender filters a table which is displayed on the form for input from the user. When the user selects the Driver, it always moves the current record to the first record; it NEVER stays on the record it should (the one the user just changed).

Thank you!
Dustin

 

[BlackKnight]

Hi,

Ok...so when the form (Dispatch_Console) loads the user can select date(s) from a calendar control to filter the records displayed on this form (Dispatch_Console). What table is filtered (T:Miles or Tispatch_Miles)? I assume one of these tables are the recordsource for the form.

Does this work correctly?

Have a good one!
BK

 

[BlackKnight]

Hi again,

I would comment out the code you have in the On Current event for now to see if the records diaply correctlt after using the calendar control. Once you have verified this then we can move on to the next step. <g>

Have a good one!
BK

 

[diddydustin]

BlackKnight,

Thank you so much for your help. The calender control already works. All it does is apply a filter by the current date. What I am trying to do is when the user changes the Driver field on the form, it will write the driver and route (with other info) to another table. All of this works, but it only writes the driver on the first record every time; it will never write the correct driver.

Thanks!
Dustin

 

[BlackKnight]

Hi,

So, when you change a driver for one of the records (the driver for that field will be changed in the underlying table) but it will also insert a new record with the new driver and info into another table. This code will onlt add a new record to table1 when the driver name is changed.

Be sure to comment out the code you have in on current event.

Private Sub Driver_AfterUpdate()
Dim conn As Connection
Dim strSQL As String

Set conn = CurrentProject.Connection
'table1 is the table you wish to add ther driver, route info whenever you change the Driver
'field on the form.
strSQL = &quot;INSERT INTO table1 (Driver, Route, Miles) VALUES ('[Driver]','[Route]',[Miles])&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(Me!Driver), &quot;&quot;, Me!Driver))
strSQL = Replace(strSQL, &quot;[Route]&quot;, IIf(IsNull(Me!Route), &quot;&quot;, Me!Route))
strSQL = Replace(strSQL, &quot;[Miles]&quot;, IIf(IsNull(Me!Miles), 0, Me!Miles))
conn.Execute strSQL
'Now, there will be a new record with teh changed info in table1.
conn.close: set conn=nothing
End Sub

Have a good one!
BK

 

[diddydustin]

BlackKnight,

You've been a huge help (the first person to help me since I started this project; this website is a blessing!) considering I have not found many resources for VBA in Access. However, I am still having the same exact problem. Here's the code I used this time:

Private Sub Combo17_AfterUpdate()

Dim db_file As String
Dim conn As ADODB.Connection

' Set to current date
DoCmd.ApplyFilter , &quot;[Date] = #&quot; & _
Me!ActiveXCtl14.Month & &quot; / &quot; & _
Me!ActiveXCtl14.Day & &quot; / &quot; & _
Me!ActiveXCtl14.Year & &quot;#&quot;

' Set the file handle
Set conn = New ADODB.Connection

' Get the database name.
db_file = &quot;C:\Documents and Settings\Dustin Lyons\Desktop\Network\Driver History.mdb&quot;

' Open a connection.
conn.ConnectionString = _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=&quot; & db_file & &quot;;&quot; & _
&quot;Persist Security Info=False&quot;
conn.Open

Dim strSQL As String

Set conn = CurrentProject.Connection

strSQL = &quot;INSERT INTO Dispatch_Miles (Driver) VALUES ('[Driver]')&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(Me!Driver), &quot;&quot;, Me!Driver))

conn.Execute strSQL

conn.Close: Set conn = Nothing

' Comment this out for now
' Find miles by state
' Set rs = conn.Execute(&quot;SELECT * FROM T:Miles WHERE Route = '&quot; & Forms!Dispatch_Console.Recordset!Route & &quot;'&quot

'rsMyTable.AddNew
'rsMyTable.Fields(&quot;Driver&quot.Value = Forms!Dispatch_Console.Recordset!Driver
'rsMyTable.Update

'For i = 1 To 15

'rsMyTable.Fields(i) = rs.Fields(i).Value + rsMyTable.Fields(i).Value
'rsMyTable.Update

'Next

End Sub

I've taken some screenshots to illustrate the problem a little better. For the 'before' shot, I've selected the third record on the form with driver 'James Hope'. I have not made any changes to the table yet.

Now, after I make the change from driver 'James Hope' to 'Calvin Issacs', the first record is automatically selected from my code and that driver 'Zach Young' is written to file. No matter what, after every update of the driver field, the first record is highlighted and gains focus.

I hope this helps a little bit. I am very confused and really have nowhere to turn but here; I've tried everything I can think of. Any time the driver field is changed, the info in the first record is always what is written to file.

Thank you so much in advance!

Dustin

 

[BlackKnight]

Hi,

I was able to change the driver from Hope to Issacs and the Issacs name etc was added as a new record to the table: Dispatch_Miles. Thanx for the additional information. Here's what I did.

BTW is your form Dispatch_Console bound to the table Dispatch_Miles? I assumed it is.

I created these tables.

Dispatch_Miles
--------------
Date
Driver
Route
Pro
Comment
F1
F2
F3

Miles
------
Route
F1
F2
F3

I made a form with a calendar control (caldate) in the header, a command button (cmdFilter) in the header to apply the filter from the calendar.

I made the recordsource of the form (Dispatch_Console) equal to the table: Dispatch_Miles. I added bound textboxes for the fields called Date, Route, Pro, Comment. I added a bound combobox (Driver). Its rowsource is based upon a table: Driver with the driver's names. It is sorted ascending.

Now, this is the ONLY code I have in the form: Copy/paste the code in to the form Dispatch_Console.

NOTE: Control names: ActiveXCtl14 is now &quot;calDate&quot;

Option Compare Database

Private Sub cmdFilter_Click()
'Set to current date
DoCmd.ApplyFilter , &quot;[Date] = #&quot; & Me!calDate.Month & &quot; / &quot; & Me!calDate.Day & &quot; / &quot; & Me!calDate.Year & &quot;#&quot;
End Sub

Private Sub Driver_Click()
'Formerly combo17.
'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver, the current route, and the sum of fields
'F1-F3 from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo Driver_Click_Err

Dim conn As Connection
Dim rsDispatch As New Recordset, rsMiles As New Recordset
Dim strSQL As String, db_file As String
Dim ARoute As String, ADriver As String
Dim F1 As Long, F2 As Long, F3 As Long

'Why is the filter being applied when you select a new driver from thec combobox.
'You should have another button on top to apply the date filter that will
'display the records with the date from ther calendar.
'DoCmd.ApplyFilter , &quot;[Date] = #&quot; & Me!calDate.Month & &quot; / &quot; & Me!calDate.Day & &quot; / &quot; & Me!calDate.Year & &quot;#&quot;

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text
Me!Driver.SetFocus
ADriver = Me!Driver.Value

Set conn = CurrentProject.Connection
rsDispatch.Open &quot;SELECT * FROM Dispatch_Miles&quot;, conn
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route='&quot; & ARoute & &quot;'&quot;, conn

'You can use an array and loop or directly assign the values since you have 15 of them.
With rsMiles
F1 = rsDispatch!F1 + !F1
F2 = rsDispatch!F2 + !F2
F3 = rsDispatch!F3 + !F3
End With

'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, Route, F1, F2, F3) VALUES ('[Driver]','[Route]',[F1], [F2], [F3])&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[Route]&quot;, IIf(IsNull(ARoute), &quot;&quot;, ARoute))
strSQL = Replace(strSQL, &quot;[F1]&quot;, F1)
strSQL = Replace(strSQL, &quot;[F2]&quot;, F2)
strSQL = Replace(strSQL, &quot;[F3]&quot;, F3)

conn.Execute strSQL
rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

Driver_Click_Exi: Exit Sub
Driver_Click_Err:
MsgBox &quot;Driver_Click: &quot; & Err.Description
Resume Driver_Click_Exi
End Sub

Have a good one!
BK

 

[BlackKnight]

Hi,

I also created a table: Driver

Driver
------
Driver

I use this as the rowsource for the Driver combobox.

Have a good one!
BK

 

[diddydustin]

BlackKnight,

You've been a great help! However, the form is bound to a 'Dispatch' table, and it writes out the new driver to the 'Dispatch_Miles' table. I've tried using your code and messing with it a bit, but it's telling me I can't use the SetFocus method. Also, when I debug, the driver and route still always equal the first record.

Here's my code:

Private Sub Combo17_Click()

Dim db_file As String
Dim conn As ADODB.Connection
Dim rsMiles As ADODB.Recordset
Dim rsDispatch As ADODB.Recordset
Dim ARoute As String, ADriver As String

' Set to current date
DoCmd.ApplyFilter , &quot;[Date] = #&quot; & _
Forms!Dispatch_Console!ActiveXCtl14.Month & &quot; / &quot; & _
Forms!Dispatch_Console!ActiveXCtl14.Day & &quot; / &quot; & _
Forms!Dispatch_Console!ActiveXCtl14.Year & &quot;#&quot;

' Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text
*** THIS IS WHERE I GET THE ERROR.
*** Cannot use SetFocus method with this object
Me!Driver.SetFocus
ADriver = Me!Driver.Value

' Set the file handle
Set conn = CurrentProject.Connection

rsDispatch.Open &quot;SELECT * FROM Dispatch_Miles&quot;, conn
rsMiles.Open &quot;SELECT * FROM T:Miles WHERE Route='&quot; & _
ARoute & &quot;'&quot;, conn

' Find miles by state
rsDispatch.AddNew
rsDispatch.Fields(&quot;Driver&quot.Value = ADriver
rsDispatch.Update

For i = 1 To 15

rsDispatch.Fields(i) = rsMiles.Fields(i).Value + rsDispatch.Fields(i).Value
rsDispatch.Update

Next

rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

End Sub

What do I need to be doing to access the data in the underlining table?

Thanks!
Dustin

 

[BlackKnight]

Hi,

Ok, I created another table: Dispatch. It is identical to Dispatch_Miles. I put all of the sample data from the screen shot into it. I deleted the records from the table Dispatch_Miles. I changed the form's recordsource to the table: Dispatch. It still worked perfectly without error using this very same code.

(1)
You are saying that the code allows you to set the focus on the Route textbox but not on combo17? Hmmmm...the textbox' and combo17 are on your form: Dispatch_Console and not in a subform right? I assume you have the view set to continuous.

(2)
Do you have any other code for combo17 events aside from what we have in the &quot;On Click&quot; event?

(3)
What is your ROWSOURCE property of combo17? Mine is:
&quot;SELECT * FROM Driver&quot;

(4)
combo17 should be bound to the Driver field in the table: Dispatch

(5)
Please for testing purposes COMMENT OUT all of your code for combo17_click. Copy/Paste this code into it AS IS. I have changed the names etc so that it should work on your machine.

Private Sub combo17_Click()
'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver, the current route, and the sum of fields
'F1-F3 from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsDispatch As New Recordset, rsMiles As New Recordset
Dim strSQL As String, db_file As String
Dim ARoute As String, ADriver As String
Dim F1 As Long, F2 As Long, F3 As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text
Me!combo17.SetFocus
ADriver = Me!combo17.Value

Set conn = CurrentProject.Connection
rsDispatch.Open &quot;SELECT * FROM Dispatch_Miles&quot;, conn
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route='&quot; & ARoute & &quot;'&quot;, conn

'You can use an array and loop or directly assign the values since you have 15 of them.
With rsMiles
F1 = rsDispatch!F1 + !F1
F2 = rsDispatch!F2 + !F2
F3 = rsDispatch!F3 + !F3
End With

'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, Route, F1, F2, F3) VALUES ('[Driver]','[Route]',[F1], [F2], [F3])&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[Route]&quot;, IIf(IsNull(ARoute), &quot;&quot;, ARoute))
strSQL = Replace(strSQL, &quot;[F1]&quot;, F1)
strSQL = Replace(strSQL, &quot;[F2]&quot;, F2)
strSQL = Replace(strSQL, &quot;[F3]&quot;, F3)

conn.Execute strSQL
rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub


Have a good one!
BK

 

[diddydustin]

BlackKnight,

Okay, I've gotten a little further. However, every time the combo17_Click fires, it always fails with the error 'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.'

Here is the code I am using:

**************************************************

Private Sub combo17_Click()
'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsDispatch As New Recordset, rsMiles As New Recordset
Dim strSQL As String, db_file As String
Dim ARoute As String, ADriver As String
Dim AL As Long, AR As Long, GA As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!Combo17.SetFocus
ADriver = Me!Combo17.Value

Set conn = CurrentProject.Connection
rsDispatch.Open &quot;SELECT * FROM Dispatch_Miles&quot;, conn
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route='&quot; & ARoute & &quot;'&quot;, conn

' Read the miles into rsMiles
With rsMiles
AL = rsDispatch!AL + !AL
AR = rsDispatch!AR + !AR
GA = rsDispatch!GA + !GA
End With

'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA) VALUES ('[Driver]',[AL], [AR], [GA])&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)

conn.Execute strSQL
rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

**************************************************

The combobox is bound to the Driver field, and it's ROWSOURCE is equal to the Driver table, not a SQL statement. The SQL statement failed with a strange error.

So I think this error helps us out a lot. Do I need to read the filtered table Dispatch into a recordset of some sort? I really am not sure where to go with this.

My Dispatch table contains the following fields:
Date, Route, Driver, Pro, Comment, ID

My Dispatch_Miles table contains the following fields:
Driver, AL, AR, GA, IL, IN, KY, LA, MI, MO, MS, OH, ON, TN, TX, WV, ID

My Driver table contains one field, Driver.

My Miles table contains the following fields:
Route, AL, AR, GA, IL, IN, KY, LA, MI, MO, MS, OH, ON, TN, TX, WV, Month (which is in the format JUNE02, or MAY02)

Finally, the only other code I have is for my calender control:

**************************************************

Private Sub ActiveXCtl14_Click()
DoCmd.ApplyFilter , &quot;[Date] = #&quot; & _
Me!ActiveXCtl14.Month & &quot; / &quot; & _
Me!ActiveXCtl14.Day & &quot; / &quot; & _
Me!ActiveXCtl14.Year & &quot;#&quot;
End Sub

**************************************************

You're help has been a lifesaver! I can't get paid until this is resolved!

Dustin

 

[BlackKnight]

Hi,

1) I changed the ROWSOURCE of combo17 to the table: Driver. It worked fine so that isn't a problem.

2) The reason you received the BOF or EOF error was due to the fact that there were no records in table: Dispatch_Miles when the code tried to use its AL, AR, GA fields in a calculation. Once I added records to the table it worked correctly.

3) I think the remaining problem is clarifying for me the calculation using the state fields like:

' Read the miles into rsMiles
' NOTE: I just added the check for null.

With rsMiles
AL = rsDispatch!AL + IIf(IsNull(!AL), 0, !AL)
AR = rsDispatch!AR + IIf(IsNull(!AR), 0, !AR)
GA = rsDispatch!AR + IIf(IsNull(!GA), 0, !GA)
End With

The problem that I see here is according to the source of rsDispatch it selects all of the records from table: Dispatch_Miles BUT it will use the same values for the miles every time for every driver selected. Of course, if the table is empty then the EOF or BOF error will occur. Do we only want to get the miles from table: Miles and simply assign it into the new record or am I missing something in the logic?


=======================================
Here's the latest version of our code:
=======================================

Private Sub combo17_Click()
'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsDispatch As New Recordset, rsMiles As New Recordset
Dim strSQL As String, db_file As String
Dim ARoute As String, ADriver As String
Dim AL As Long, AR As Long, GA As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!combo17.SetFocus
ADriver = Me!combo17.Value

Set conn = CurrentProject.Connection
rsDispatch.Open &quot;SELECT * FROM Dispatch_Miles&quot;, conn
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route='&quot; & ARoute & &quot;'&quot;, conn

' Read the miles into rsMiles
' NOTE: I added the check for NULL.
With rsMiles
AL = rsDispatch!AL + IIf(IsNull(!AL), 0, !AL)
AR = rsDispatch!AR + IIf(IsNull(!AR), 0, !AR)
GA = rsDispatch!AR + IIf(IsNull(!GA), 0, !GA)
End With

'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA) VALUES ('[Driver]',[AL], [AR], [GA])&quot;
strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)

conn.Execute strSQL
rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

Have a good one!
BK

 

[diddydustin]

Okay, now it won't run because it's saying I have a syntax error in the INSERT INTO statement. Here's my code:

*********************************************************

Private Sub combo17_Click()

'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsDispatch As New Recordset, rsMiles As New Recordset
Dim strSQL As String, db_file As String
Dim ARoute As String, ADriver As String

Dim AL As Long, AR As Long, GA As Long
Dim IL As Long, vIN As Long, KY As Long
Dim LA As Long, MI As Long, MO As Long
Dim MS As Long, OH As Long, vON As Long
Dim TN As Long, TX As Long, WV As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!Combo17.SetFocus
ADriver = Me!Combo17.Value

Set conn = CurrentProject.Connection
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route = '&quot; & ARoute & &quot;'&quot;, conn
' Read the miles into rsMiles
' NOTE: I added the check for NULL.

AL = IIf(IsNull(rsMiles!AL), 0, rsMiles!AL)
AR = rsMiles!AR + IIf(IsNull(rsMiles!AR), 0, rsMiles!AR)
GA = rsMiles!GA + IIf(IsNull(rsMiles!GA), 0, rsMiles!GA)
IL = rsMiles!IL + IIf(IsNull(rsMiles!IL), 0, rsMiles!IL)

vIN = IIf(IsNull(rsMiles!IN), 0, rsMiles!IN)
KY = IIf(IsNull(rsMiles!KY), 0, rsMiles!KY)
LA = IIf(IsNull(rsMiles!LA), 0, rsMiles!LA)
MI = IIf(IsNull(rsMiles!MI), 0, rsMiles!MI)
MO = IIf(IsNull(rsMiles!MO), 0, rsMiles!MO)
MS = IIf(IsNull(rsMiles!MS), 0, rsMiles!MS)
OH = IIf(IsNull(rsMiles!OH), 0, rsMiles!OH)

vON = IIf(IsNull(rsMiles!ON), 0, rsMiles!ON)
TN = IIf(IsNull(rsMiles!TN), 0, rsMiles!TN)
TX = IIf(IsNull(rsMiles!TX), 0, rsMiles!TX)
WV = IIf(IsNull(rsMiles!WV), 0, rsMiles!WV)


'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA, IL, IN, KY, LA, MI, MO, MS, OH, ON, TN, TX, WV) VALUES &quot; & _
&quot;('[Driver]', [AL], [AR], [GA], [IL], [IN], [KY], [LA], [MI], [MO], [MS], [OH], [ON], [TN], [TX], [WV])&quot;

strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)
strSQL = Replace(strSQL, &quot;[IL]&quot;, IL)
strSQL = Replace(strSQL, &quot;[IN]&quot;, vIN)
strSQL = Replace(strSQL, &quot;[KY]&quot;, KY)
strSQL = Replace(strSQL, &quot;[LA]&quot;, LA)
strSQL = Replace(strSQL, &quot;[MI]&quot;, MI)
strSQL = Replace(strSQL, &quot;[MO]&quot;, MO)
strSQL = Replace(strSQL, &quot;[MS]&quot;, MS)
strSQL = Replace(strSQL, &quot;[OH]&quot;, OH)
strSQL = Replace(strSQL, &quot;[ON]&quot;, vON)
strSQL = Replace(strSQL, &quot;[TN]&quot;, TN)
strSQL = Replace(strSQL, &quot;[TX]&quot;, TX)
strSQL = Replace(strSQL, &quot;[WV]&quot;, WV)

Debug.Print (strSQL)
conn.Execute strSQL

rsDispatch.Close: rsMiles.Close
Set rsDispatch = Nothing: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

*********************************************************

Debug prints this as strSQL before we try to execute it:

INSERT INTO Dispatch_Miles (Driver, AL, AR, GA, IL, IN, KY, LA, MI, MO, MS, OH, ON, TN, TX, WV) VALUES ('Calvin Issacs', 0, 0, 0, 0, 0, 184, 0, 0, 0, 0, 0, 0, 0, 0, 0)

I've tried tagging a semi-colon at the end, which did nothing... there is one more field in the Dispatch_Miles table, but that is an auto number for ID purposes. I'm not accounting for that, but I don't think that would matter either. I'm trying to get it to work now. Can you see anything in this code that would be causing that?

Dustin

 

[diddydustin]

Here is a cleaned up version of my code:

Private Sub combo17_Click()

'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsMiles As New Recordset
Dim strSQL As String
Dim ARoute As String, ADriver As String

Dim AL As Long, AR As Long, GA As Long
Dim IL As Long, vIN As Long, KY As Long
Dim LA As Long, MI As Long, MO As Long
Dim MS As Long, OH As Long, vON As Long
Dim TN As Long, TX As Long, WV As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!Combo17.SetFocus
ADriver = Me!Combo17.Value

Set conn = CurrentProject.Connection
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route = '&quot; & ARoute & &quot;'&quot;, conn
' Read the miles into rsMiles
' NOTE: I added the check for NULL.

AL = IIf(IsNull(rsMiles!AL), 0, rsMiles!AL)
AR = IIf(IsNull(rsMiles!AR), 0, rsMiles!AR)
GA = IIf(IsNull(rsMiles!GA), 0, rsMiles!GA)
IL = IIf(IsNull(rsMiles!IL), 0, rsMiles!IL)

vIN = IIf(IsNull(rsMiles!IN), 0, rsMiles!IN)
KY = IIf(IsNull(rsMiles!KY), 0, rsMiles!KY)
LA = IIf(IsNull(rsMiles!LA), 0, rsMiles!LA)
MI = IIf(IsNull(rsMiles!MI), 0, rsMiles!MI)
MO = IIf(IsNull(rsMiles!MO), 0, rsMiles!MO)
MS = IIf(IsNull(rsMiles!MS), 0, rsMiles!MS)
OH = IIf(IsNull(rsMiles!OH), 0, rsMiles!OH)

vON = IIf(IsNull(rsMiles!ON), 0, rsMiles!ON)
TN = IIf(IsNull(rsMiles!TN), 0, rsMiles!TN)
TX = IIf(IsNull(rsMiles!TX), 0, rsMiles!TX)
WV = IIf(IsNull(rsMiles!WV), 0, rsMiles!WV)


'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA, IL, IN, KY, LA, MI, MO, MS, OH, ON, TN, TX, WV) VALUES &quot; & _
&quot;('[Driver]', [AL], [AR], [GA], [IL], [IN], [KY], [LA], [MI], [MO], [MS], [OH], [ON], [TN], [TX], [WV])&quot;

strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)
strSQL = Replace(strSQL, &quot;[IL]&quot;, IL)
strSQL = Replace(strSQL, &quot;[IN]&quot;, vIN)
strSQL = Replace(strSQL, &quot;[KY]&quot;, KY)
strSQL = Replace(strSQL, &quot;[LA]&quot;, LA)
strSQL = Replace(strSQL, &quot;[MI]&quot;, MI)
strSQL = Replace(strSQL, &quot;[MO]&quot;, MO)
strSQL = Replace(strSQL, &quot;[MS]&quot;, MS)
strSQL = Replace(strSQL, &quot;[OH]&quot;, OH)
strSQL = Replace(strSQL, &quot;[ON]&quot;, vON)
strSQL = Replace(strSQL, &quot;[TN]&quot;, TN)
strSQL = Replace(strSQL, &quot;[TX]&quot;, TX)
strSQL = Replace(strSQL, &quot;[WV]&quot;, WV)

Debug.Print (strSQL)
conn.Execute strSQL

rsMiles.Close: Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

 

[BlackKnight]

Hi,

ok...the reason for the SQL syntax error was that both fields IN and ON are reserved words. To use them we have to put [] around them. I had to make the replace function replace the strings &quot;[INDIANA]&quot; and &quot;OREGON&quot;.

I have written a small function for you to include in the form called: &quot;NZ&quot;. It makes the null checks easier.

I eliminated the rsDispatch since you were no longing using it.

Here's the latest code based upon what you gave me in the last mesasage with my changes and additions.

=================================
Private Sub combo17_Click()

'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsMiles As New Recordset
Dim strSQL As String
Dim ARoute As String, ADriver As String

Dim AL As Long, AR As Long, GA As Long
Dim IL As Long, vIN As Long, KY As Long
Dim LA As Long, MI As Long, MO As Long
Dim MS As Long, OH As Long, vON As Long
Dim TN As Long, TX As Long, WV As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!combo17.SetFocus
ADriver = Me!combo17.Value

Set conn = CurrentProject.Connection
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route = '&quot; & ARoute & &quot;'&quot;, conn
' Read the miles into rsMiles
' NOTE: I added the check for NULL.

'NOTE: I changed from using iif() to a function NZ(). Copy/paste
' the function NZ() into the general declarations
' section of the form.
'=====================================================
With rsMiles
AL = NZ(!AL, 0) + NZ(!AL, 0)
AR = NZ(!AL, 0) + NZ(!AR, 0)
GA = NZ(!GA, 0) + NZ(!GA, 0)
IL = NZ(!IL, 0) + NZ(!IL, 0)
vIN = NZ(!IN, 0) + NZ(!IN, 0)
KY = NZ(!KY, 0) + NZ(!KY, 0)
LA = NZ(!LA, 0) + NZ(!LA, 0)
MI = NZ(!MI, 0) + NZ(!MI, 0)
MO = NZ(!MO, 0) + NZ(!MO, 0)
MS = NZ(!MS, 0) + NZ(!MS, 0)
OH = NZ(!OH, 0) + NZ(!OH, 0)
vON = NZ(!ON, 0) + NZ(!ON, 0)
TN = NZ(!TN, 0) + NZ(!TN, 0)
TX = NZ(!TX, 0) + NZ(!TX, 0)
WV = NZ(!WV, 0) + NZ(!WV, 0)
End With
'=====================================================
'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA, IL, [IN], KY, LA, MI, MO, MS, OH, [ON], TN, TX, WV) VALUES &quot; & _
&quot;('[Driver]', [AL], [AR], [GA], [IL], [INDIANA], [KY], [LA], [MI], [MO], [MS], [OH], [OREGON], [TN], [TX], [WV])&quot;

strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)
strSQL = Replace(strSQL, &quot;[IL]&quot;, IL)
strSQL = Replace(strSQL, &quot;[INDIANA]&quot;, vIN)
strSQL = Replace(strSQL, &quot;[KY]&quot;, KY)
strSQL = Replace(strSQL, &quot;[LA]&quot;, LA)
strSQL = Replace(strSQL, &quot;[MI]&quot;, MI)
strSQL = Replace(strSQL, &quot;[MO]&quot;, MO)
strSQL = Replace(strSQL, &quot;[MS]&quot;, MS)
strSQL = Replace(strSQL, &quot;[OH]&quot;, OH)
strSQL = Replace(strSQL, &quot;[OREGON]&quot;, vON)
strSQL = Replace(strSQL, &quot;[TN]&quot;, TN)
strSQL = Replace(strSQL, &quot;[TX]&quot;, TX)
strSQL = Replace(strSQL, &quot;[WV]&quot;, WV)

Debug.Print (strSQL)
conn.Execute strSQL

rsMiles.Close
Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

Private Function NZ(ByVal AVal As Variant, ByVal Retval As Variant) As Variant
'AVal is a valeu to be tested for NULL.
'Retval is a value you want to return if AVal is null.

Dim Result As Variant

If IsNull(AVal) Then
Result = Retval
Else
Result = AVal
End If
NZ = Result
End Function
=================================

Have a good one!
BK

 

[BlackKnight]

Hi again,

Here is the cleaned up version of my code. <g>

Private Sub combo17_Click()

'This event procedure will be fired whenever the driver in the combobox is clicked.
'When this happens, the newly selected driver and the sum of fields
'AL, AR, and GA from both the Miles and Dispatch_Miles tables will be inserted as one record into the
'table Dispatch_Miles.
'
On Error GoTo combo17_Click_Err

Dim conn As Connection
Dim rsMiles As New Recordset
Dim strSQL As String
Dim ARoute As String, ADriver As String

Dim AL As Long, AR As Long, GA As Long
Dim IL As Long, vIN As Long, KY As Long
Dim LA As Long, MI As Long, MO As Long
Dim MS As Long, OH As Long, vON As Long
Dim TN As Long, TX As Long, WV As Long

'Assign controls contents to a variable for clarity.
Me!Route.SetFocus
ARoute = Me!Route.Text

Me!combo17.SetFocus
ADriver = Me!combo17.Value

Set conn = CurrentProject.Connection
rsMiles.Open &quot;SELECT * FROM Miles WHERE Route = '&quot; & ARoute & &quot;'&quot;, conn
' Read the miles into rsMiles
' NOTE: I added the check for NULL.

'NOTE: I changed from using iif() to a function NZ(). Copy/paste
' the function NZ() into the general declarations
' section of the form.
'=====================================================
With rsMiles
AL = NZ(!AL, 0)
AR = NZ(!AR, 0)
GA = NZ(!GA, 0)
IL = NZ(!IL, 0)
vIN = NZ(!IN, 0)
KY = NZ(!KY, 0)
LA = NZ(!LA, 0)
MI = NZ(!MI, 0)
MO = NZ(!MO, 0)
MS = NZ(!MS, 0)
OH = NZ(!OH, 0)
vON = NZ(!ON, 0)
TN = NZ(!TN, 0)
TX = NZ(!TX, 0)
WV = NZ(!WV, 0)
End With
'=====================================================
'Build INSERT statement.
strSQL = &quot;INSERT INTO Dispatch_Miles (Driver, AL, AR, GA, IL, [IN], KY, LA, MI, MO, MS, OH, [ON], TN, TX, WV) VALUES &quot; & _
&quot;('[Driver]', [AL], [AR], [GA], [IL], [INDIANA], [KY], [LA], [MI], [MO], [MS], [OH], [OREGON], [TN], [TX], [WV])&quot;

strSQL = Replace(strSQL, &quot;[Driver]&quot;, IIf(IsNull(ADriver), &quot;&quot;, ADriver))
strSQL = Replace(strSQL, &quot;[AL]&quot;, AL)
strSQL = Replace(strSQL, &quot;[AR]&quot;, AR)
strSQL = Replace(strSQL, &quot;[GA]&quot;, GA)
strSQL = Replace(strSQL, &quot;[IL]&quot;, IL)
strSQL = Replace(strSQL, &quot;[INDIANA]&quot;, vIN)
strSQL = Replace(strSQL, &quot;[KY]&quot;, KY)
strSQL = Replace(strSQL, &quot;[LA]&quot;, LA)
strSQL = Replace(strSQL, &quot;[MI]&quot;, MI)
strSQL = Replace(strSQL, &quot;[MO]&quot;, MO)
strSQL = Replace(strSQL, &quot;[MS]&quot;, MS)
strSQL = Replace(strSQL, &quot;[OH]&quot;, OH)
strSQL = Replace(strSQL, &quot;[OREGON]&quot;, vON)
strSQL = Replace(strSQL, &quot;[TN]&quot;, TN)
strSQL = Replace(strSQL, &quot;[TX]&quot;, TX)
strSQL = Replace(strSQL, &quot;[WV]&quot;, WV)

Debug.Print (strSQL)
conn.Execute strSQL

rsMiles.Close
Set rsMiles = Nothing
conn.Close: Set conn = Nothing

combo17_Click_Exi: Exit Sub
combo17_Click_Err:
MsgBox &quot;combo17_Click: &quot; & Err.Description
Resume combo17_Click_Exi
End Sub

Private Function NZ(ByVal AVal As Variant, ByVal Retval As Variant) As Variant
'AVal is a valeu to be tested for NULL.
'Retval is a value you want to return if AVal is null.

Dim Result As Variant

If IsNull(AVal) Then
Result = Retval
Else
Result = AVal
End If
NZ = Result
End Function

Have a good one!
BK

 

[diddydustin]

BlackKnight,

Sorry it's taken me so long to get back to you but I went out of town for a few days. Your post has been very helpful! I'm doing much more with this database, so maybe once I'm done with the work (or have any other questions? ) I'll show you what I've done.

Thanks so much!
Dustin