Variable in a message box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have an unbound form which I use to search too see if a record already
exists. I have set up a message box which displays when this is the case.
Message box currently reads "Record Found"

How can I get it to show the record it has found in the body of the message
some thing like "Record ABC1234 found" where ABC1234 is my record.

Any help on how I should construct the syntax of my message box would be
welcome. I hope this is clear but I can post the code I am currently using if
neccessary.

Many thanks

Chris
 
How can I get it to show the record it has found in the body of the
message some thing like "Record ABC1234 found" where ABC1234 is my
record.
Click to expand...


' see if it's an empty recorset
If Not rst.BOF Then
' no -- then there is a valid record
strPrompt = "Message found " & rst.MessageField
MsgBox strPrompt, vbOkOnly or vbExclamation

Else
' no existing record... can go ahead

End If


Hope that helps


Tim F
 
Tim

Thanks for trying to help. Imay not have made it clear what I was trying to
achieve. My code is working fine.It checks for a record ok but I want my
resulting message box to show what it was searching for.In my particular
database my unbound form asks for a car registration no to be input before a
new record can be set up. The query checks the record set as these numbers
are not allowed to be duplicated. So the user clicks a command button, this
opens the form asking for the reg no of the car say ABC1234. This record is
found and the message box says "A client with this Car Reg already exists". I
would like it to read "A client with car reg ABC1234 already exists"(or
whatever was typed into the form). As I say the code I have works fine I am
just having trouble with the syntax of the message box and whilst not crucial
would allow my interface to look a bit more polished. Anyway here is the code
I am using:

Private Sub cmdOK_Click()
Dim strCarReg As String
Dim qry As QueryDef
Dim NumRecords As Byte
On Error GoTo Error_Trap:

Me.txtCarReg.SetFocus
strCarReg = Me.txtCarReg.Text
Set qry = CurrentDb.QueryDefs("qryCarReg")
qry.Parameters("CarReg").Value = strCarReg

Dim Rs As DAO.Recordset
Set Rs = qry.OpenRecordset

Rs.MoveLast
NumRecords = Rs.RecordCount

Check_Number:


If NumRecords > 0 Then
'This means the Car Reg already exists
Dim ViewRecord As Integer
ViewRecord = MsgBox("A client with this Car Reg already exists! View
record?", vbExclamation + vbYesNo, "VALIDATION ALERT. EXISTING RECORD FOUND")

If ViewRecord = vbYes Then
'Open clients form and go to record
Dim strFilter As String
strFilter = "CarReg='" & strCarReg & "'"
DoCmd.Close acForm, "frmInputCarReg"
DoCmd.OpenForm "frmCLIENTDATAENTRY", , , strFilter
Forms("frmCLIENTDATAENTRY").Surname.SetFocus
Forms("frmCLIENTDATAENTRY").cmdFindClient.Enabled = False


Else

txtCarReg.SetFocus
txtCarReg.Value = Null
cmdOK.Enabled = False
cmdReset.Enabled = False
End If

Else

Forms("frmCLIENTDATAENTRY").SetFocus

DoCmd.GoToRecord , , acNewRec

Forms("frmCLIENTDATAENTRY").CarReg.SetFocus
Forms("frmCLIENTDATAENTRY").CarReg.Text = strCarReg
Forms("frmCLIENTDATAENTRY").Surname.SetFocus
Close_Form

End If

Exit Sub


Error_Trap:
If Err.Number = 3021 Then
NumRecords = 0
Resume Check_Number
Else
MsgBox Err.Number & vbCrLf & Err.Description

Exit Sub
End If



End Sub


Private Sub Command20_Click()

End Sub

Private Sub cmdReset_Click()
txtCarReg.SetFocus
txtCarReg.Text = ""
cmdOK.Enabled = False
cmdReset.Enabled = False
End Sub

Private Sub Form_Open(Cancel As Integer)
Me. CarReg.SetFocus
cmdReset.Enabled = False
End Sub



Private Sub txtCarReg_Exit(Cancel As Integer)
If Me.txtCarReg.Text = "" Then
Me.cmdOK.Enabled = False
Me.cmdReset.Enabled = False
Else
Me.cmdOK.Enabled = True
Me.cmdReset.Enabled = True
End If

End Sub
 
Ok i have worked out how to show the variable at the end of my message in the
message box with this "A record has been found for" & strCarReg etc, but does
anyone know if its possible to put the variable in the middle of the message
for example like this "A record has been found for" &strCarReg "and you
cannot create a new record"

If anyone knows the correct syntax to do this I would be very grateful.

Thanks in advance

Chris
 
for example like this "A record has been found for" &strCarReg "and you
cannot create a new record"
Click to expand...

strPrompt = "A record has been found for " & strCarReg & _
"and you cannot create another one."


You can look up help files for the & operator, or am I missing some other
problem here?

B Wishes


Tim F
 
Tim thank you for your help and patience. Sorry about failing to follow the
protocol I do realise people here give freely of their time and I for one am
always grateful for any assistance I get, I am fairly new to VBA but trying
to improve. Am I right in saying all I need to do is declare a new variable
like this Dim StrPrompt as string then another line of code strPrompt = "A
record has been found for " & strCarReg & _
"and you cannot create another one."then just have my message box like this :MsgBox ( &strPrompt ,+vbOk)
Click to expand...

Thanks again
Chris
 
Tim thanks for your help. I tried your latest answer and it works beautifully
now!

Thanks again

Chris
 
Sorry about failing to
follow the protocol I do realise people here give freely of their time
Click to expand...

No failure: I guessed you were new to VBA but I did not want to insult
you in the case you had a more subtle problem that I had missed!
Am I right in saying all I need to
do is declare a new variable like this Dim StrPrompt as string then
another line of code strPrompt = "A record has been found for " &
Click to expand...

It is just as legal to do something like

MsgBox "A record has been found for " & etc & etc, vbOkOnly

but I find that splitting out all the bits makes it much easier to read.
When you come back to the same program six months later in order to
update it, you'll notice the difference then!

All the best


Tim F
 
Back
Top