VBA question

[Bearacade]

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

[Bearacade]

I changed the Else to just end the if. I am still coming up with the similar problem. I know I am putting something in wrong. Forwhatever reason, it us bypassing the assigned to code when checkbox is 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 & " - " & IDstamp & 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 & " - " & IDstamp & vbCrLf & " " & Textbox & vbCrLf & Timestamp & " *** Message Closed by " & IDstamp & " ***" & vbCrLf
End If

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

End Sub

[Dave Peterson]

If this is for Access, then shouldn't you ask in an Access newsgroup?

Bearacade wrote:
>
> 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
>
> --
> Bearacade
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=562219

--

Dave Peterson

[Dave Peterson]

If this is for Access, then shouldn't you ask in an Access newsgroup?

Bearacade wrote:
>
> 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
>
> --
> Bearacade
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=562219

--

Dave Peterson

[Dave Peterson]

If this is for Access, then shouldn't you ask in an Access newsgroup?

Bearacade wrote:
>
> 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
>
> --
> Bearacade
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=562219

--

Dave Peterson

[[email protected]]

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 Peterson wrote:
> If this is for Access, then shouldn't you ask in an Access newsgroup?
>
> Bearacade wrote:
> >
> > 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
> >
> > --
> > Bearacade
> >
> > ------------------------------------------------------------------------
> > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > View this thread: http://www.excelforum.com/showthread...hreadid=562219
>
> --
>
> Dave Peterson

[Dave Peterson]

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



[email protected] wrote:
>
> 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 Peterson wrote:
> > If this is for Access, then shouldn't you ask in an Access newsgroup?
> >
> > Bearacade wrote:
> > >
> > > 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
> > >
> > > --
> > > Bearacade
> > >
> > > ------------------------------------------------------------------------
> > > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> > > View this thread: http://www.excelforum.com/showthread...hreadid=562219
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Dave Peterson]

If this is for Access, then shouldn't you ask in an Access newsgroup?

Bearacade wrote:
>
> 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
>
> --
> Bearacade
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=562219

--

Dave Peterson