Need Help with VBA code (1 Viewer)

johnsonhuo · Mar 12, 2018

I really need help with my VBA code

problem 1

Dim sBody As String
Dim rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset("Select * From OilSampleEmail")
While Not rst1.EOF
sBody = "<html><body>" & sBody & Nz(rst1!Set, "") & " " & Nz(rst1!Car, "") & "</body></html>"
rst1.MoveNext

Wend

With this one, the output always has one blank line after the first line, and the rest are normal. The output result is like
AAA

BBB
CCC
DDD

but the expected result should be
AAA
BBB
CCC
DDD

I have no idea how this could happen with my code.

Problem 2

Private Sub Command16_Click()
If Me.Text6.Value = "Null" Then
MsgBox "Please input Set Number"
ElseIf Me.Text10 = "" Then
MsgBox "Please input Car Number"

Else
CurrentDb.Execute "insert into OilSampleEmail ([Set], [Car]) values ('" & Me.Text6 & "', '" & Me.Text10 & "')"
Command18_Click
End If

For this one, it is similar to problem 1. Every first time I open this form, the 'if function' does not work at all. All conditions stated by 'if function' don't apply. However, the rest input will be fine with the conditions.

Thank you very much for your help!

nhorton79 · Mar 12, 2018

Still learning myself but in your 2nd problem try:
<CODE>
If nz(Me.Text6,"") = "" Then
Msgbox code
Elseif nz(Me.Text10,"") = "" Then
Msgbox code
Else
Code as you had it...
End If
</CODE>

Normally you would just say Null,
rather than "Null", which is a string.

Sent from my iPhone using Tapatalk

johnsonhuo · Mar 12, 2018

nhorton79 said:
Still learning myself but in your 2nd problem try:
<CODE>
If nz(Me.Text6,"") = "" Then
Msgbox code
Elseif nz(Me.Text10,"") = "" Then
Msgbox code
Else
Code as you had it...
End If
</CODE>

Normally you would just say Null,
rather than "Null", which is a string.

Thank you very much nhorton79. Your code works perfectly and it solved my second problem.

arnelgp · Mar 12, 2018

Dim sTemp as string

sBody = "<html><body>"
While Not rst1.EOF
sTemp=Nz(rst1!Set, "") & " " & Nz(rst1!Car, "")
If trim(sTemp)<>"" then
sBody=sbody & "" & sTemp & ""
End if
rst1.MoveNext

Wend
sBody=sBody & "</body></html>"

Mark_ · Mar 12, 2018

johnsonhuo said:

I really need help with my VBA code

problem 1

Code:

Dim sBody As String
Dim rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset("Select * From OilSampleEmail")
While Not rst1.EOF
    sBody = "<html><body><p>" & sBody & Nz(rst1!Set, "") & "  " & Nz(rst1!Car, "") & "</p></body></html>"
 rst1.MoveNext

Wend

For myself, I would validate what data you have. Prior to setting sBody I would do something to verify what data you do have. Arnel did give you a way to avoid having the blank line but you may also need to validate your data. I'd also add an "ORDER" statement to your SELECT so you can present the data in a more logical order than simply record order.

johnsonhuo · Mar 14, 2018

arnelgp said:
Dim sTemp as string

sBody = "<html><body>"
While Not rst1.EOF
sTemp=Nz(rst1!Set, "") & " " & Nz(rst1!Car, "")
If trim(sTemp)<>"" then
sBody=sbody & "" & sTemp & ""
End if
rst1.MoveNext

Wend
sBody=sBody & "</body></html>"

Hi arnelgp, thank you very much for your help and it does work well after using your code. I think both my problems are related to the string length and it was really great to have you to help me solve it.

johnsonhuo · Mar 14, 2018

Mark_ said:
For myself, I would validate what data you have. Prior to setting sBody I would do something to verify what data you do have. Arnel did give you a way to avoid having the blank line but you may also need to validate your data. I'd also add an "ORDER" statement to your SELECT so you can present the data in a more logical order than simply record order.

Hi mark, thank you for your advice. It is very professional and useful. As a beginner, I really made many mistakes and could not consider all scenarios. I will follow your instructions and do things in a better way while programming.

I have another concern about your comment in regards of the “Order” statement. I tried to use select * from sBody order by Set but it does not work at all for this one. Do you have some suggestion? Thank you very much in advance!

arnelgp · Mar 14, 2018

Your original code will work if if you add filter to your select statement:

Set rs1=currentdb.openrecordset("select * from oilsampleEmail where Trim([set] & [car] & '')<>'')")

Mark_ · Mar 14, 2018

johnsonhuo said:
Hi mark, thank you for your advice. It is very professional and useful. As a beginner, I really made many mistakes and could not consider all scenarios. I will follow your instructions and do things in a better way while programming.

I have another concern about your comment in regards of the “Order” statement. I tried to use select * from sBody order by Set but it does not work at all for this one. Do you have some suggestion? Thank you very much in advance!

It would not involve sBody itself as that is the variable you are using to build your HTML.

For myself, rather than using

Code:

Set rst1 = db.OpenRecordset("Select * From OilSampleEmail")

I would use

Code:

DIM asSQL as STRING
asSQL = "Select * From OilSampleEmail"
Set rst1 = db.OpenRecordset(asSQL)

This way you can use debug.print or msgbox to see what your SQL is prior to issuing the set. This is very useful when you are building the SQL string from pieces, such as values from a form or data from a record.

It would then become

Code:

DIM asSQL as STRING
asSQL = "Select * From OilSampleEmail ORDER BY [YourField]"
Set rst1 = db.OpenRecordset(asSQL)

I do wonder about part of your code though. What are you trying to do with sBody at the end?

nhorton79 · Mar 14, 2018

Thank you very much nhorton79. Your code works perfectly and it solved my second problem.[/QUOTE said:
No problem. Glad I could help out.

Sent from my iPhone using Tapatalk

johnsonhuo · Mar 19, 2018

Mark_ said:
It would not involve sBody itself as that is the variable you are using to build your HTML.

For myself, rather than using

Code:

Set rst1 = db.OpenRecordset("Select * From OilSampleEmail")

I would use

Code:

DIM asSQL as STRING asSQL = "Select * From OilSampleEmail" Set rst1 = db.OpenRecordset(asSQL)

This way you can use debug.print or msgbox to see what your SQL is prior to issuing the set. This is very useful when you are building the SQL string from pieces, such as values from a form or data from a record.

It would then become

Code:

DIM asSQL as STRING asSQL = "Select * From OilSampleEmail ORDER BY [YourField]" Set rst1 = db.OpenRecordset(asSQL)

I do wonder about part of your code though. What are you trying to do with sBody at the end?

Hi Mark, Thank you again for your very clear and professional explanation for the set order part. I used your method and it does work very well. Additionally, the debug print is a very powerful tool for error finding and testing. It is really helpful for beginners like me.

For the sBody, it is used for an automatically generated email that directed quotes some input data.

Thanks again for your help and I really appreciate it!

johnsonhuo · Mar 19, 2018

arnelgp said:
Your original code will work if if you add filter to your select statement:

Set rs1=currentdb.openrecordset("select * from oilsampleEmail where Trim([set] & [car] & '')<>'')")

Thank again you arnelgp for your advice. Trimming the leading and trailing space could be my problem as well. It will be very helpful for some raw data!

Thank you again for your help and it is so great to have you here!

Need Help with VBA code (1 Viewer)

johnsonhuo

Registered User.

nhorton79

Registered User.

johnsonhuo

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Mark_

Longboard on the internet

johnsonhuo

Registered User.

johnsonhuo

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Mark_

Longboard on the internet

nhorton79

Registered User.

johnsonhuo

Registered User.

johnsonhuo

Registered User.

Similar threads

Users who are viewing this thread