newbie help-assign value to query

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
I'm playing around with some SQL queries and need a bit of help to get me going.
I have a table named 2984. 1st column is ServiceNo which is number values stepping up in increments of 0.5.
I want to find the largest value in the column, then add 2 to that value and enter a new record with the new value. The rest I'm sure i will be able to work out. The code I've been playing with is below. I know why it doesn't work but I don't know how to make it work. I want to get the value and call it Dte then I can manipulte the value and hopefuly add a new record with my new value. Any help would be great. thanks

Code:
Sub New_Day_Member_Table()
Dim Dty As Single
Dim Dte As Single
DoCmd.SetWarnings False
Dte=DoCmd.RunSQL "SELECT MAX(ServiceNo)FROM 2984;"  ' i want to assign Dte to the value of the highest value in ServiceNo
    Dty = Dte + 2
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 2 & " ', ""Day"");"
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 0.5 & " ', ""Night"");"
End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
I believe this could be resolved with a nested query approach - although, since after insert #1 the max value changes ... you have a new max value to work with in insert #2 ... so ...

Code:
Sub New_Day_Member_Table()
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(((SELECT MAX(ServiceNo)FROM 2984) + 2), ""Day"");"
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(((SELECT MAX(ServiceNo)FROM 2984) [COLOR="Blue"]- 1.5[/COLOR]), ""Night"");"
End Sub

Does that work? Note that here I assume you have numbers in the ServiceNo column - which may not be true! (I removed the single quotes).

Another approach, more like what you have, is to dump the value of the query result into a recordset - that way you can access its value and use it in your subroutine. In this case, I'll assume you have text values for service number, as your syntax suggests:

Code:
Sub New_Day_Member_Table()
Dim Dty As Single
Dim Dte As Single
Dim rs As DAO.Recordset
DoCmd.SetWarnings False
Set rs = CurrentDb.OpenRecordset("SELECT MAX(ServiceNo)FROM 2984;", dbOpenForwardOnly, dbReadOnly, dbReadOnly)
    
If Not rs.EOF Then
    Dty = CDbl(rs.Fields(0).Value + 2)
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 2 & " ', ""Day"");"
    DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 0.5 & " ', ""Night"");"
Else
    MsgBox "Error: Max ServiceNo Not found."
    GoTo My_Exit:
End If
    

My_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
End Sub
 

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
xenou
thankyou for your reply, i'll play with both and see what happens.
Would you please explain what the following mean so i can understand whats going on; .EOF and CDbl ? thanks again.
 

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
2nd sub works perfectly (thanks) but the first one doesn't. Error is;
Run-Time error 3000
"Reserved error(-3025); there is no message for this error"
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

On the error, seems like a problem with my insert statement. We'd have to try some sample variations on it to see if we can get one that works...which might be unnecessary now anyway (the other solution does the trick).

On the lines in the code CDbl and EOF, the former is a function to convert a string value to a numeric value that can hold decimals - "Cast" [String] to "Double". This is needed so that we can arithmetic with the numbers (if your numbers are already real numbers, its redundant - I'm not sure because you've enclosed single quotes around your service numbers in the table, which suggest text values instead of numeric values. EOF means "End of File", though in the case its really "End of Recordset" - if the recordset returns a value, it should not be EOF, and if no records are returned, it would be EOF. So, it basically just checks that we actually got a record back (which is unlikely unlikely to not happen here, but ... just old habit).

The Recordset is an invisible, programmatic object with the query results, which in this case would be one record with one value - the max service number.

HTH,
ξ
 

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
I've done some excel programming (novice)and i don't know if i'm missing something or asking the something which doesn't apply to access;
ie. in excel i can

dim vle as integer
vle=range("A1").value
vle=vle +1
vle=vle*2
range("A2").value=vle

once i have called a cell value something (vle in this case) i can pretty much do what i want with that value.

I'm hoping i can do the same with an access record field value; call it sometihing and manipulate it, use it in calculations and re enter the result into new records or existing ones etc; but so far i can't find a way to give the field value i query a name to work with.

Am i missing something straighforward or going off in a direction of total misunderstanding?

i'd appreciate some help to get going
cheers
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
Here's the rub:
Code:
vle=range("A1").value

Access doesn't give you ranges as a means to access a value in a table, therefore, you have to go with a somewhat different object model ...

In our example, we used a recordset object for this purpose - first, use SQL to pull data from the database and create a recordset in memory, then use the recordset programmatically to capture a key value of interest (service number). And finally, when we've completed our processing, we use SQL to write our change back to the database for permanent storage. As an aside, using SQL is very important when working with databases programmatically, being the "native language" of relational databases - all part of the the "game" with databases.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top