VBA question

B

Bearacade

This is not an excel question but a VBA one. I want to firstl
apologize for that but I know there are a lot of VBA gurus around.

I am trying to populate some fields in Access depending on what th
user puts in. To keep the example clear, I have a Message Bo
(MessHist), A Text Box (Textbox), a Combo Box (NewAssign) and a Chec
Box (Check44).

When a button is pressed, it first makes

MessHist = MessHist & Textbox

then check if NewAssign is a new value
If yes, assign new value, attach message to MessHist, if no, leav
Messhist alone

Then check if checkbox is checked
If yes, assign new value, attach message to MessHist, if no, leav
MessHist alone.

Here is the code I have .. the problem I run into is when I have a ne
value in NewAssign AND the checkbox checked. The two messaged are no
being generated. But it works fine if only the NewAssign value change
or Checkbox is checked. any help would be greatly appreciated

Private Sub BtnSbmt2_Click()

Dim Temp As String
Dim IDstamp As String
Dim Timestamp As String

Timestamp = Date & " " & Time()
IDstamp = DLookup("[FirstName] & ' ' & [LastName]", "Contacts"
"[ID] = " & [OpenedBy])

MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
vbCrLf & " " & Textbox & vbCrLf

If NewAssign <> AssignedTo Then
AssignedTo = NewAssign
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstam
& vbCrLf & " " & Textbox & vbCrLf & vbCrLf & Timestamp & " **
Message Re-assigned to " & DLookup("[FirstName] & ' ' & [LastName]"
"Contacts", "[ID] = " & [AssignedTo]) & " ***" & vbCrLf
Else
If Check44.Value = True Then
Status = "Closed"
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstam
& vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Messag
Closed by " & IDstamp & " ***" & vbCrLf
End If
End If

Textbox.Value = Null
Me.Refresh

End Su
 
B

Bearacade

I changed the Else to just end the if. I am still coming up with th
similar problem. I know I am putting something in wrong. Forwhateve
reason, it us bypassing the assigned to code when checkbox i
checked...

Private Sub BtnSbmt2_Click()

Dim Temp As String
Dim IDstamp As String
Dim Timestamp As String

Timestamp = Date & " " & Time()
IDstamp = DLookup("[FirstName] & ' ' & [LastName]", "Contacts"
"[ID] = " & [OpenedBy])

MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
vbCrLf & " " & Textbox & vbCrLf

If NewAssign <> AssignedTo Then
AssignedTo = NewAssign
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstam
& vbCrLf & " " & Textbox & vbCrLf & vbCrLf & Timestamp & " **
Message Re-assigned to " & DLookup("[FirstName] & ' ' & [LastName]"
"Contacts", "[ID] = " & [AssignedTo]) & " ***" & vbCrLf
End If

If Check44.Value = True Then
Status = "Closed"
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstam
& vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Messag
Closed by " & IDstamp & " ***" & vbCrLf
End If

Textbox.Value = Null
NewAssign.Value = Null
Check44.Value = Null
Me.Refresh

End Su
 
D

Dave Peterson

If this is for Access, then shouldn't you ask in an Access newsgroup?
This is not an excel question but a VBA one. I want to firstly
apologize for that but I know there are a lot of VBA gurus around.

I am trying to populate some fields in Access depending on what the
user puts in. To keep the example clear, I have a Message Box
(MessHist), A Text Box (Textbox), a Combo Box (NewAssign) and a Check
Box (Check44).

When a button is pressed, it first makes

MessHist = MessHist & Textbox

then check if NewAssign is a new value
If yes, assign new value, attach message to MessHist, if no, leave
Messhist alone

Then check if checkbox is checked
If yes, assign new value, attach message to MessHist, if no, leave
MessHist alone.

Here is the code I have .. the problem I run into is when I have a new
value in NewAssign AND the checkbox checked. The two messaged are not
being generated. But it works fine if only the NewAssign value changed
or Checkbox is checked. any help would be greatly appreciated

Private Sub BtnSbmt2_Click()

Dim Temp As String
Dim IDstamp As String
Dim Timestamp As String

Timestamp = Date & " " & Time()
IDstamp = DLookup("[FirstName] & ' ' & [LastName]", "Contacts",
"[ID] = " & [OpenedBy])

MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp &
vbCrLf & " " & Textbox & vbCrLf

If NewAssign <> AssignedTo Then
AssignedTo = NewAssign
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & vbCrLf & Timestamp & " ***
Message Re-assigned to " & DLookup("[FirstName] & ' ' & [LastName]",
"Contacts", "[ID] = " & [AssignedTo]) & " ***" & vbCrLf
Else
If Check44.Value = True Then
Status = "Closed"
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Message
Closed by " & IDstamp & " ***" & vbCrLf
End If
End If

Textbox.Value = Null
Me.Refresh

End Sub
Click to expand...
 
P

pointies

You are right, and I did ask in other access forums. I weren't able to
find the help that I need and the VBA codes are fairly similar in both
applications. I know it's a something I am overlooking or I am
missing, probably just syntax. Perhaps you can help me out? Thanks in
advance.

Dave said:
If this is for Access, then shouldn't you ask in an Access newsgroup?
This is not an excel question but a VBA one. I want to firstly
apologize for that but I know there are a lot of VBA gurus around.

I am trying to populate some fields in Access depending on what the
user puts in. To keep the example clear, I have a Message Box
(MessHist), A Text Box (Textbox), a Combo Box (NewAssign) and a Check
Box (Check44).

When a button is pressed, it first makes

MessHist = MessHist & Textbox

then check if NewAssign is a new value
If yes, assign new value, attach message to MessHist, if no, leave
Messhist alone

Then check if checkbox is checked
If yes, assign new value, attach message to MessHist, if no, leave
MessHist alone.

Here is the code I have .. the problem I run into is when I have a new
value in NewAssign AND the checkbox checked. The two messaged are not
being generated. But it works fine if only the NewAssign value changed
or Checkbox is checked. any help would be greatly appreciated

Private Sub BtnSbmt2_Click()

Dim Temp As String
Dim IDstamp As String
Dim Timestamp As String

Timestamp = Date & " " & Time()
IDstamp = DLookup("[FirstName] & ' ' & [LastName]", "Contacts",
"[ID] = " & [OpenedBy])

MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp &
vbCrLf & " " & Textbox & vbCrLf

If NewAssign <> AssignedTo Then
AssignedTo = NewAssign
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & vbCrLf & Timestamp & " ***
Message Re-assigned to " & DLookup("[FirstName] & ' ' & [LastName]",
"Contacts", "[ID] = " & [AssignedTo]) & " ***" & vbCrLf
Else
If Check44.Value = True Then
Status = "Closed"
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Message
Closed by " & IDstamp & " ***" & vbCrLf
End If
End If

Textbox.Value = Null
Me.Refresh

End Sub
Click to expand...
Click to expand...
 
D

Dave Peterson

I don't use Access, so I don't have a guess.



You are right, and I did ask in other access forums. I weren't able to
find the help that I need and the VBA codes are fairly similar in both
applications. I know it's a something I am overlooking or I am
missing, probably just syntax. Perhaps you can help me out? Thanks in
advance.

Dave said:
If this is for Access, then shouldn't you ask in an Access newsgroup?
This is not an excel question but a VBA one. I want to firstly
apologize for that but I know there are a lot of VBA gurus around.

I am trying to populate some fields in Access depending on what the
user puts in. To keep the example clear, I have a Message Box
(MessHist), A Text Box (Textbox), a Combo Box (NewAssign) and a Check
Box (Check44).

When a button is pressed, it first makes

MessHist = MessHist & Textbox

then check if NewAssign is a new value
If yes, assign new value, attach message to MessHist, if no, leave
Messhist alone

Then check if checkbox is checked
If yes, assign new value, attach message to MessHist, if no, leave
MessHist alone.

Here is the code I have .. the problem I run into is when I have a new
value in NewAssign AND the checkbox checked. The two messaged are not
being generated. But it works fine if only the NewAssign value changed
or Checkbox is checked. any help would be greatly appreciated

Private Sub BtnSbmt2_Click()

Dim Temp As String
Dim IDstamp As String
Dim Timestamp As String

Timestamp = Date & " " & Time()
IDstamp = DLookup("[FirstName] & ' ' & [LastName]", "Contacts",
"[ID] = " & [OpenedBy])

MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp &
vbCrLf & " " & Textbox & vbCrLf

If NewAssign <> AssignedTo Then
AssignedTo = NewAssign
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & vbCrLf & Timestamp & " ***
Message Re-assigned to " & DLookup("[FirstName] & ' ' & [LastName]",
"Contacts", "[ID] = " & [AssignedTo]) & " ***" & vbCrLf
Else
If Check44.Value = True Then
Status = "Closed"
MessHist = MessHist & vbCrLf & Timestamp & " - " & IDstamp
& vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Message
Closed by " & IDstamp & " ***" & vbCrLf
End If
End If

Textbox.Value = Null
Me.Refresh

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA question.. 1
Creating VBA Functions When Creating Spreadsheet Via VBA? 6
Error handling for missing Outlook attachments 1
If then and Case Select 1
VBA Code to create reminders in Outlook from Excel 0
Automation Error: Object Disconnected From Clients 1
Forward Engineer Visio ER Diagram to SQL DDL 2
Outlook vba errror message says Access can't find my userform 5

Top