DATA TYPE CONVERSION Error with VBa

I

Icebot53

Registered User.
Local time
Today, 09:07
Joined
Sep 21, 2015
Messages
14
DATA TYPE CONVERSION Error with VBA - Solved

Good day,

I have the following code attached the the click event of a button.

I am receiving a Data Type Conversion on this part of the code:

!Quantity = "txt" & (a)

Any help to solve this will be appreciate.

Code: 
Private Sub Command17_Click()

Dim db As Database
Dim rs As Recordset
Dim DocNr, TransType, Site, TransDate, HireStart, Comments As String
'I am naming going to do the code in three parts for the three column in the form
Dim a, b, c As Long
'Column 1
Dim cmb1, cmb2, cmb3, cmb4, cmb5, cmb6, cmb7, cmb8, cmb9, cmb10, cmb11 As String
Dim txt1, txt2, txt3, txt4, txt5, txt6, txt7, txt8, txt9, txt10, txt11 As String


Set db = CurrentDb()
'this opens up the database the form is in. Leave as is.
Set rs = db.OpenRecordset("tbl_transactionlist")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s
DocNr = Nz(Me.txt_docnr, "")
TransType = Nz(Me.cmb_transtype, "")
Site = Nz(Me.cmb_site, "")
TransDate = Nz(Me.txt_transdate, "")
HireStart = Nz(Me.txt_hirestart, "")
Comments = Nz(Me.txt_comments, "")




'Main If to check if document information is there
If DocNr = "" Then
MsgBox ("You have not completed the Document Number. Please include this and retry.")
Exit Sub
Else
End If
If TransType = "" Then
MsgBox ("You have not selected a transaction type. Please include this and retry.")
Exit Sub
Else
End If
If Site = "" Then
MsgBox ("You have not chosen a site for the transaction. Please include this and retry.")
Exit Sub
Else
End If
If TransDate = "" Then
MsgBox ("You have not selected a transaction date. Please include this and retry.")
Exit Sub
Else
End If
If HireStart = "" Then
MsgBox ("You have not selected a hire start date. Please include this and retry.")
Exit Sub
Else
End If




'this is for the main document information
With rs
'This implies that there is rs before each . or !
    .AddNew
    !Doc_nr = DocNr
    !Trans_Type = TransType
    !Site = Site
    !Date_Of_Transaction = TransDate
    !Hire_Start_Date = HireStart
    !Comments = Comments
    .Update
    .Close
End With


'this will save the first column to the table
Set rs = db.OpenRecordset("tbl_transactions")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s


a = 1
cmb1 = Nz(Me.Combo1, "")
cmb2 = Nz(Me.Combo2, "")
cmb3 = Nz(Me.Combo3, "")
cmb4 = Nz(Me.Combo4, "")
cmb5 = Nz(Me.Combo5, "")
cmb6 = Nz(Me.Combo6, "")
cmb7 = Nz(Me.Combo7, "")
cmb8 = Nz(Me.Combo8, "")
cmb9 = Nz(Me.Combo9, "")
cmb10 = Nz(Me.Combo10, "")
cmd11 = Nz(Me.Combo11, "")
txt1 = Nz(Me.Text1, "")
txt2 = Nz(Me.Text2, "")
txt3 = Nz(Me.Text3, "")
txt4 = Nz(Me.Text4, "")
txt5 = Nz(Me.Text5, "")
txt6 = Nz(Me.Text6, "")
txt7 = Nz(Me.Text7, "")
txt8 = Nz(Me.Text8, "")
txt9 = Nz(Me.Text9, "")
txt10 = Nz(Me.Text10, "")
txt11 = Nz(Me.Text11, "")


With rs


For c = 1 To 11
If "cmb" & (a) <> "" Then
If "txt" & (a) <> "" Then
.AddNew
    !Doc_nr = DocNr
    !Item_Code = "cmb" & (a)
    !Quantity = "txt" & (a)
    .Update
Else
End If
Else
End If
a = a + 1
Next c
    .Close
End With




MsgBox "This transaction has been saved.", vbOKOnly
DoCmd.Close acForm, "frm_capture_new_transaction", acSaveNo


Set db = Nothing
Set rs = Nothing
'These close the recordset
End Sub
 
Last edited:
I haven't read all your code yet but one question for you

If a=1, why not write
Code: 
!Quantity=me.txt1
etc

Otherwise try something like
Code: 
!quantity=me("txt1")&a

You may have to tweak this a bit as I'm typing this on a phone so there could be typos
 
ridders said:
I haven't read all your code yet but one question for you

If a=1, why not write
Code: 
!Quantity=me.txt1
etc

Otherwise try something like
Code: 
!quantity=me("txt1")&a

You may have to tweak this a bit as I'm typing this on a phone so there could be typos
Click to expand...

Hi,

I use the variable later on in ' a = a + 1'

There are more than 30 text boxes and 30 combo boxes to be used. That just speeds up the coding process for me.

Maybe I am wrong? Any advice will be appreciated.
 
I'm sure you could tidy it up but I suggest you explain in more detail what you are trying to do so that someone here can advise

In the meantime, try the corrected version from my first post:

Code: 
!quantity=me("txt1"&a)

I put the brackets in the wrong place originally

Also do a, b, c etc really need to be long? Will they get that big?

You could also consider a loop like
Code: 
For a=1 to [whatever the max number of a is or a function to determine it...]
... your code here....
Next
 
Hi Ridders,

It doesn't work. If I use your code, it says "Error 2465 - Microsoft Access can;t find the field 'txt11' referred to in your expression.

See, I don't have too much experience with Access. In Excel I always dimmed my a,b,c, as long as that was the way the example on the net showed and I just continued.

If I could upload the Database maybe it will help?
 
See, I named my combo boxes and my text boxes like this:

Combo1
Text1
Combo2
Text2

Then the save button must saved each combo box and text box combo as a single record.

So I was thinking of just doing something like this:



A = 1

For c = 1 to 11
!Product_Code = combobox & a
!Quantity = txtbox & a

a = a + 1
next c
 
This sounds like possibly poorly stored data, or something that could easily be achieved though a single update query.

I have to say though - if your quantity field is a number you are trying to store a string with text in it which won't work.

As Ridders has suggested - perhaps you could describe in plain English, no database jargon, exactly what you are trying to do.
 
Okay:

I have a form which contains a delivery note number, delivery date, and then a bunch of combo boxes to select which items were delivered (using the item code). Then there are text boxes next to the combo boxes to record how many of that specific item was delivered on this delivery note.

Once completed, a save button is clicked, and all data is stored to 2 tables:

1) 1 Table Containing All the Different delivery note numbers, sites and dates.
2) 1 Table that contains all the different items delivered per delivery note number.

Does this help?
 
That makes sense. I don't think you can refer to a combo using an array like you are trying to.
You have to reference the control as a control, something like
!Quantity = Me.Controls("txt" & a)

But this sounds like you have up to 11 rows you can update. Would this not be better done in a continuous form? Then you simply add as many items as you have once the header information is added.
 
Minty said:
That makes sense. I don't think you can refer to a combo using an array like you are trying to.
You have to reference the control as a control, something like
!Quantity = Me.Controls("txt" & a)

But this sounds like you have up to 11 rows you can update. Would this not be better done in a continuous form? Then you simply add as many items as you have once the header information is added.
Click to expand...


There will be a total of 33 possible records. On the form there are 3 columns of 2 x Input Boxes.

Attached is the database.
 

Attachments

Last edited:
You can post here - you just need to zip the file.
 
Icebot: on a side note, there's something you should be aware of, because it can inadvertently cause problems from time to time.

Your variable declarations. You need to be aware that in VBA, the Dim statement doesn't quite work the way you seem to think it does. Instead, any variable not explicitly declared as something else is automatically created as a variant.

That means that
Code: 
Dim a, b, c As Long
is functionally identical to
Code: 
Dim a As Variant, b As Variant, c As Long

This means that it is quite possible to accidentally assign strings or even objects to variables you THINK are numeric, which can result in unanticipated errors when you turn around and try to manipulate them as numbers or even save them to a numeric field.
 
Okay that looks neat, but you are making it very hard work for yourself.
I would use a bound form and sub form, with the top part as the you have with the transaction header (transactionlist), and a bound subform for the transactions.

No need to do any complex recordset manoeuvring, and should you suddenly have 34 or 46 items on a delivery no need to reinvent the wheel.
 
Icebot53 said:
I have a form which contains a delivery note number, delivery date, and then a bunch of combo boxes to select which items were delivered (using the item code). Then there are text boxes next to the combo boxes to record how many of that specific item was delivered on this delivery note.
Click to expand...

The items should be stored in a related table as a separate record for each item and displayed in a subform.
 
More than happy to leave the discussion to my various colleagues as I'm sure there is a better way of doing this now I've read the extra info since I last posted.

However, returning to post #5:

It doesn't work. If I use your code, it says "Error 2465 - Microsoft Access can;t find the field 'txt11' referred to in your expression.
Click to expand...

That suggests it worked for txt1 to txt10 & that you haven't got a control called txt11
If I'm right, add it as your code is for 1 to 11
Similarly with cmb

Then my code should work as long as its not an infinite loop:
Code: 
 !Item_Code = Me("cmb1" & a)
    !quantity=Me("txt1" & a)

I know this notation looks odd but I have used it for years & it does work
 
Code: 
For c = 1 To 11
	.AddNew
		!Doc_nr = DocNr
		!Item_Code = "" & me("combo" & c)
		!Quantity = "" & me("text" & c)
	.Update
Next

Adding records like this is very slow. This would be better..

Code: 
For c = 1 To 11
	sql = "insert into tbl_transactions (Doc_nr,Item_Code,Quantity) values (1,2,3)"
	sql = replace(sql,"1",nz(DocNr,0))
	sql = replace(sql,"2",nz(me("combo" & c),0))
	sql = replace(sql,"3",nz(me("text" & c),0))
	
	currentdb.execute sql
Next

I can't work out from your code what datatype your fields are. If they are text you need quotes around the values.

sql = "insert into tbl_transactions (Doc_nr,Item_Code,Quantity) values ('1','2','3')"
 
static said:
Code: 
For c = 1 To 11
	.AddNew
		!Doc_nr = DocNr
		!Item_Code = "" & me("combo" & c)
		!Quantity = "" & me("text" & c)
	.Update
Next

Adding records like this is very slow. This would be better..

Code: 
For c = 1 To 11
	sql = "insert into tbl_transactions (Doc_nr,Item_Code,Quantity) values (1,2,3)"
	sql = replace(sql,"1",nz(DocNr,0))
	sql = replace(sql,"2",nz(me("combo" & c),0))
	sql = replace(sql,"3",nz(me("text" & c),0))
	
	currentdb.execute sql
Next

I can't work out from your code what datatype your fields are. If they are text you need quotes around the values.

sql = "insert into tbl_transactions (Doc_nr,Item_Code,Quantity) values ('1','2','3')"
Click to expand...

Hi,

I came right with another solution, but you are saying that the way I am doing it is slow. Slow in which way? Will it cause problems later with the Database or is it only when you are adding large volumes of records at once?

The way you are doing it seems interesting however, I am still quite new to this so I will need to google to understand what all the arguments are.
 
static said:
Code: 
For c = 1 To 11
    .AddNew
        !Doc_nr = DocNr
        !Item_Code = "" & me("combo" & c)
        !Quantity = "" & me("text" & c)
    .Update
Next
Adding records like this is very slow. This would be better..

Code: 
For c = 1 To 11
    sql = "insert into tbl_transactions (Doc_nr,Item_Code,Quantity) values (1,2,3)"
    sql = replace(sql,"1",nz(DocNr,0))
    sql = replace(sql,"2",nz(me("combo" & c),0))
    sql = replace(sql,"3",nz(me("text" & c),0))
    
    currentdb.execute sql
Next
Click to expand...

Adding records one at a time via a recordset is much the same speed as inserting them with the same number of queries.

When we talk about queries being faster than recordsets, it is different context where the database engine is processing sets of records.
 
Good day,

I came right by using the following code:

Code: 
Private Sub Command17_Click()

Dim db As Database
Dim rs As Recordset
Dim DocNr, TransType, Site, TransDate, HireStart, Comments As String
'I am naming going to do the code in three parts for the three column in the form
Dim a, b, c As Long
'Column 1

Set db = CurrentDb()
'this opens up the database the form is in. Leave as is.
Set rs = db.OpenRecordset("tbl_transactionlist")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s
DocNr = Nz(Me.txt_docnr, "")
TransType = Nz(Me.cmb_transtype, "")
Site = Nz(Me.cmb_site, "")
TransDate = Nz(Me.txt_transdate, "")
HireStart = Nz(Me.txt_hirestart, "")
Comments = Nz(Me.txt_comments, "")


'Main If to check if document information is there
If DocNr = "" Then
MsgBox ("You have not completed the Document Number. Please include this and retry.")
Exit Sub
Else
End If
If TransType = "" Then
MsgBox ("You have not selected a transaction type. Please include this and retry.")
Exit Sub
Else
End If
If Site = "" Then
MsgBox ("You have not chosen a site for the transaction. Please include this and retry.")
Exit Sub
Else
End If
If TransDate = "" Then
MsgBox ("You have not selected a transaction date. Please include this and retry.")
Exit Sub
Else
End If
If HireStart = "" Then
MsgBox ("You have not selected a hire start date. Please include this and retry.")
Exit Sub
Else
End If


'this is for the main document information
With rs
'This implies that there is rs before each . or !
    .AddNew
    !Doc_nr = DocNr
    !Trans_Type = TransType
    !Site = Site
    !Date_Of_Transaction = TransDate
    !Hire_Start_Date = HireStart
    !Comments = Comments
    .Update
    .Close
End With

'this will save the first column to the table
Set rs = db.OpenRecordset("tbl_transactions")
'and this opens up the recordset you want to add the records to. It is the name of the table in "s

a = 1

[COLOR="Red"]With rs

For c = 1 To 33
If Me("combo" & a) <> "" Then
If Me("text" & a) <> "" Then
.AddNew
    !Doc_nr = DocNr
    !Item_Code = Me("combo" & a)
    !Quantity = Me("text" & a)
    .Update
Else
End If
Else
End If
a = a + 1
Next c
    .Close
End With[/COLOR]


MsgBox "This transaction has been saved.", vbOKOnly
DoCmd.Close acForm, "frm_capture_new_transaction", acSaveNo

Set db = Nothing
Set rs = Nothing
'These close the recordset
End Sub
 
You must log in or register to reply here.

Similar threads

N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
2,748
tvanstiphout
T
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
1,604
tvanstiphout
T
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
1,071
CJ_London
C
S
Issue with Access Data Right after saving
Replies
12
Views
1,195
Gasman
Gasman
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
597
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom