Prompt a user to fill in Certain fields 2

[MA04]

Hi all,

I have a search input form, which has a command button which runs an append query in sql like so:

Private Sub Append_Click()

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME ) " & _
"SELECT [Forms]![Ma_search2]![Expr1] AS Expr1, [Forms]![Ma_search2]![Expr2] AS Expr2, [Forms]![Ma_search2]![Expr3] AS Expr3, [Forms]![Ma_search2]![postcode] AS Expr4, [Forms]![Ma_search2]![Name_input] AS Expr5; "

DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub

I want to add a piece of code that will prompt the user to fill in textbox Name_input, SAL_input, type, country, source and Phone before running query, i.e. all these text and combo boxes have to be filled before appending. Any help very much appreciated, Thanks in advance,

M-.

 

[MaxEd]

MA04,

You can try using a module to verify that those data fields are not null on your form. If they are then you could concatenate a string that includes the list of datda fields that are not filled.

-Laughter works miracles.

 

[MA04]

Thanks for the reply MaxEd, but can i not put code into the above procedure in my first thread so code could be like,

If textbox Name_input and SAL_input and Phone = null then
messagebox 'you have to enter all personal info before appending'
else if combobox type and Country and Source = null then
Message 'You have to populate all comboboxes befor e appending'
else
run query code from my first thread.

Does that make sense, is it possible to achieve this?
Thanks again,
M-.

 

[MaxEd]

MA04,

You can try string up the entire field by doing.

Dim strTemp as string

if isnull([Forms]![Ma_search2]![Expr1]) then
strTemp="Expr 1 " & vbcrlf
end if

if isnull([Forms]!Ma_search2]![Expr2]) then
strTemp=strTemp & "Expr 2 " & vbcrlf
end if

if isnull([Forms]![Ma_search2]![Expr3]) then
strTemp=strTemp & "Expr 3 " & vbcrlf
end if

if isnull([Forms]![Ma_search2]![postcode]) then
strTemp=strTemp & "Post Code " & vbcrlf
end if

if isnull([Forms]![Ma_search2]![Name_input]) then
strTemp=strTemp & "Name " & vbcrlf
end if

if not(isnull(strTemp)) then
msgbox "The following fields are missing" & vbcrlf & strtemp
end if



-Laughter works miracles.

 

[MA04]

Thanks for the help MaxEd that works,

M-.

 

[TheAceMan1]

How are ya MA04 . . . . .

I'd completed the code but forgot to post it! Here it is anyway . . .

[blue]Private Sub Append_Click()
   Dim ctl As Control, Ctls As String, SL As String, DL As String
   Dim Msg As String, Style As Integer, Title As String, strSQL As String

   SL = vbNewLine
   DL = SL & SL
   Ctls = "Name_Input Sal_Input Type Country Source Phone"
   
   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
         If InStr(1, Ctls, ctl.Name) > 0 And Trim(ctl & "") = "" Then
            Msg = "Required Data Entry Missing!" & DL & _
                  "Data Entry is Required for the following:" & DL & _
                  "   Country" & SL & _
                  "   Name_Input" & SL & _
                  "   Phone" & SL & _
                  "   Sal_Input" & SL & _
                  "   Source" & SL & _
                  "   Type" & DL & _
                  "You won't be able to continue otherwise!"
            Style = vbInformation + vbOKOnly
            Title = "Required Data Error! . . ."
            MsgBox Msg, Style, Title
            ctl.SetFocus
            Exit Sub
         End If
      End If
   Next
   
   DoCmd.SetWarnings False
   
   strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME ) " & _
            "SELECT " & [Forms]![Ma_search2]![Expr1] & " AS Expr1, " _
                      & [Forms]![Ma_search2]![Expr2] & " AS Expr2, " _
                      & [Forms]![Ma_search2]![Expr3] & " AS Expr3, " _
                      & [Forms]![Ma_search2]![postcode] & " AS Expr4, " _
                      & [Forms]![Ma_search2]![Name_Input] & " AS Expr5;"
   DoCmd.RunSQL strSQL
   
   DoCmd.SetWarnings True
End Sub[/blue]

See Ya! . . . . . .

 

[MA04]

Hi AceMan,

Thanks for the code, i seem to get an error:
'Run-time error 3075''syntax error (missing operator) in query expression 'displays A1 value'. The debug highlights DoCmd.RunSQL strSQL.

Any ideas to as what the problem may be? Thanks,
M-.

 

[MA04]

Thanks Ace,

If i replace this:

strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME ) " & _
"SELECT " & [Forms]![Ma_search2]![Expr1] & " AS Expr1, " _
& [Forms]![Ma_search2]![Expr2] & " AS Expr2, " _
& [Forms]![Ma_search2]![Expr3] & " AS Expr3, " _
& [Forms]![Ma_search2]![postcode] & " AS Expr4, " _
& [Forms]![Ma_search2]![Name_Input] & " AS Expr5;"

with this:

strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME ) " & _
"SELECT [Forms]![Ma_search2]![Expr1] AS Expr1, [Forms]![Ma_search2]![Expr2] AS Expr2, [Forms]![Ma_search2]![Expr3] AS Expr3, [Forms]![Ma_search2]![postcode] AS Expr4, [Forms]![Ma_search2]![Name_input] AS Expr5; "

it works.
M-.