Solved: need help with 3 things

[Dark-Angrl]

Well 1 thing i realy need to firgure out how to make it work, others should go more smothly once i firgure out this one. Im trying to make at my work a database of serial numbers, the program keep track of the serial numbers to know where a certain card is to know witch one it need to print out. First thing im trying to do is to fetch the last serial number but i keep getting errors no mather what i do. here what i have now

Dim strSQL As String
Dim Table_name As Variant
Dim Last_result As Variant
Dim Bg As Variant
Dim Reponse As VbMsgBoxResult
Dim Reponse_bidon As VbMsgBoxResult
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset

Set db = CurrentDb()
Table_name = Me![Ibox_Card]
Bg = Chr(34)
Response = MsgBox("Do you want to generate this card " & Bg & "" & Table_name & "" & Bg & " serial numbers?", vbQuestion + vbYesNo)
If Response = vbYes Then
strSQL = "SELECT TOP 1 " & Table_name & _
" FROM " & Table_name & _
" WHERE 'Serial_Number' " & _
"ORDER BY 'Serial_Number' DESC;"
Set rst1 = db.OpenRecordset(strSQL, dbOpenDynaset)
Last_result = CVar(rst1)

Reponse_bidon = MsgBox("" & Last_result & "", vbCritical + vbOKOnly)

rst.Close
Set rst = Nothing
End If

for now im trying to test if it can fetch the last one. Depending on the errors i get i can tell the user the card doesnt exist. where Reponse_bidon it says invalide arguement. I have the impression my openrecordset isnt working as intended.

2 other thing are simple, to creat the serial number in this maner 10 number are the card number i need to add 4 digits at the end that are counted in hex, i also tag along info i have in input boxes that stay the same for each serial number. After the number are generated i need it to be shown in a form the list of serial number that are going to be printed in a special paper format "small .25 inch by 1 inch sticker that has .1 inch on each side of none usable space" once the person click print it save the data in the correct table and prints it.

First one is the one i have most difficulty with, the rest i just need some help "pointers" to get it done quickly and nicely, i can probebly firgure out most of it by surfing the net just the first one that is waisting my time.

[Mavyak]

Shouldn't this line:

strSQL = "SELECT TOP 1 " & Table_name & _

actually read:

strSQL = "SELECT TOP 1 Serial_Number " & _

[CreganTur]

Welcome to the forum- always good to have new members!

First off, please wrap your code in VBA tags (Click the green VBA button). This will format your code according to VBIDE and make it easier to read ;)

I see a few different issues that could be keeping your code from working. The main one is the fact that your WHERE condition is incorrect. If Serial Number is a field name, then it does not need the single quotes and you haven't given it anything to evaluate. If Serial_Number is a value, then you have a value to evaluate but no field to evaluate.

I'm guessing that your serial numbers are either the table's primary key, or they are indexed to allow No Duplicates.

You do not need the OrderBy section of your SQL statement since you are only wanting to return a single record.

Why are you querying your form instead of the underlying table?


Since you're just wanting to get the last serial number in your table there's a much cleaner solution available, by using the MoveLast method. Try this:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LastResult

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName")
rst.MoveLast
LastResult = rst!FieldName

MsgBox "'" & LastResult & "'", vbCritical

rst.Close
Set rst = Nothing
Set db = Nothing
replace TableName with the name of the table that holds your serial numbers, and replace FieldName with the name of your serial number field.

Test the above code and see if it gives you what you want.

[Dark-Angrl]

Thanks alot for the quick reply. Sorry for not putting my code in the green box.

My table names are the cards number, sor like that if you have a card called 8900195 well the table has this name. So like that when the user types in the card number it fetches the right table.

As indexing and primary key. Arent thoese automaticly generated and cannot be controlled by the user? if you can control what goes in them how do i generate a table with that fields indexing or primay key? and what is the diffrence btw both of them? heres my code for creating my tables

DoCmd.RunSQL "CREATE TABLE " & Table_name & _
"([Serial_Number] TEXT(14) , [Work_Order] NUMBER, [Eco] NUMBER, [Date] DATETIME);"

i will try your piece of code and see if it works.

[Dark-Angrl]

Omg i cant belive i spent 8h trying to firgure this out :banghead: thanks alot :thumb gonna keep you guys posted on my progress and what bumps i get stuck on. And its perfect this generates errors i need to simplify my code.

[CreganTur]

Glad we could help :)

If this issue is resolved, then you can mark the thread as solved by clicking on Thread Tools at the top of the page, and then select 'Mark this thread as solved'.

If you come across a new issue, just start a new thread and we'll do our best to help :thumb