Dear All,
first of all, i would say this the best forum of ms access for biginners.
my issue is i have multilble text box in my form & based on change in one of this text box i need the code to compare between data in the form & table & returm Msg if it is not matching.
attached screen FYI.
i hope this will not big issue.
i look in the internet but i could not figuer out the VBA code since i do not know VBA. what comes to my mind to to use select case.
If the welder field was a combobox then you could just have all the required data as columns and then they are there to test with whenever you need them.
Dear Paul,
My requirement is like that.
i need to fill the data in the form, once i reach welderA the code/dlookup start comparing the data in the form based on the data in tb
example:
if i put 2 in pipe dia after welder update the code shall check if the value is within the range of Dai Max & Dia Min if not Msg will say this welder is not qualified.
the same think with thik
for the welding process/Type of Material just need to check if the value is same
Dear Paul,
My requirement is like that.
i need to fill the data in the form, once i reach welderA the code/dlookup start comparing the data in the form based on the data in tb
example:
if i put 2 in pipe dia after welder update the code shall check if the value is within the range of Dai Max & Dia Min if not Msg will say this welder is not qualified.
the same think with thik
for the welding process/Type of Material just need to check if the value is same
I agree with the others that a ComboBox would be a better way to approach your problem. There are at least two reasons I can think of: 1. You are sure that you select a valid Welder ID in each field and 2. The ComboBox columns can be set to include all of the comparison fields from the table row (as James Dudden is pointing out).
However, you may have a valid reason for wanting to use TextBoxes to enter the Welder IDs, in which case you need the DLookup function to retrieve the comparison fields for each Welder (although I think a Recordset might fit your needs better in this case).
Before going further, I strongly recommend that you clean up your field and table names. For example, the field 'Pipe(Ø)' breaks the rules by using both parentheses and phi symbol within the name. Much better to use something like 'wtPipeDiameter' (wt prefix to show it's Welder table – you can choose any naming convention you like, but be consistent). Other field names have embedded spaces, which makes life more difficult in the programming and general management. In short, don't use anything other than alphanumeric characters in names.
So let's consider the two approaches.
Approach 1: ComboBox
For this approach to work, you will need a Primary Key field in your Welder_tb table. Add an Autonumber field in the first row of the table design. Access will assign numbers for the Primary Key automagically.
Replace your four Welder TextBoxes with Combo Boxes (right-click on each, select "Change To" and then "Combo Box").
Configure each ComboBox as follows (you can select all four of them and modify the properties as a group):
In the Data tab, set the RowSource to "SELECT * FROM Welder_tb;". This will return all 11 columns.
Set the Bound Column to 1 (the key added to the table, as above)
You could set Limit To List = Yes if you want to ensure only valid names can be selected.
In the Format tab, set the Column Count to 11.
Set the column widths to 0;0;0;2;0;0;0;0;0;0;0. This hides all but the 'Stamp N' column in the drop-down. My unit of measure is cm, so if you use a different unit, choose an appropriate number in place of the 2.
In the Event tab, set the On Click event to '=checkWelder()'
Approach 2: TextBox
In the Event tab of your four TextBoxes, set the Before Update event to '=checkWelder()'
No other changes are necessary here.
In both cases:
Now go to the Form's module and enter the following:
Code:
[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]Private Function checkWelder()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim strMessage As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim ctl As Control[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Rem set ctl to point to the active form control[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set ctl = ActiveControl[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Rem check that it is a combo box and validate fields[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If ctl.ControlType = acComboBox Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem this version is for combo box controls[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Dim intLoop As Integer[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Rem the ComboBox columns correspond with its RecordSet (0 to 10 in this case)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' For intLoop = 0 To ctl.ColumnCount - 1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Rem this displays each column, its field name and value[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' MsgBox "Column: " & intLoop + 1 & ", Field: " & ctl.Recordset.Fields(intLoop).Name & ", Value: " & ctl.Column(intLoop), vbInformation, ctl.Name[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]' Next[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] strMessage = ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem put validation checks here[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If (Val(Me.wtPipeDiameter) < Val(Nz(ctl.Column(4), 0))) Or (Val(Me.wtPipeDiameter) > Val(Nz(ctl.Column(5), 0))) Then strMessage = strMessage & vbCr & "Pipe diameter" ' col 5 is Dia Min; col 6 is Dia Max (zero-relative columns)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem show message if any error field added to list[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If Not strMessage = "" Then MsgBox "Errors:" & strMessage, vbCritical, "Data Error"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]ElseIf ctl.ControlType = acTextBox Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem this version is for textbox controls[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Dim rst As Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Set rst = CurrentDb.OpenRecordset("SELECT * FROM Welder_tb WHERE [Stamp N]='" & ctl & "';")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If Not rst.EOF Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] strMessage = ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem put validation checks here[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If (Val(Me.wtPipeDiameter) < Val(Nz(rst![Dia Min], 0))) Or (Val(Me.wtPipeDiameter) > Val(Nz(rst![Dia Max], 0))) Then strMessage = strMessage & vbCr & "Pipe diameter" ' col 5 is Dia Min; col 6 is Dia Max (zero-relative columns)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Rem show message if any error field added to list[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If Not strMessage = "" Then MsgBox "Errors:" & strMessage, vbCritical, "Data Error"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR][/COLOR][/FONT]
The above handles both ComboBox and TextBox events, but in practice you would choose just one control type for your solution. Having decided upon which control type you want, just remove the code for the other type.
The commented-out code might be useful for you to see the column details for the ComboBox. If you remove the comment flags, you will get a message for each of the 11 column fields.
I have just included the test for pipe diameter in my example – you can add other checks in a similar fashion for the other validations. The point is to build the message from all fields so that you get a single display of all error fields for the form.
The ComboBox is a little more complex to set up, but it's the better solution IMHO.
The screen resolution is too small to read clearly, but I can see your Combo Boxes are shown as 'Unbound'. I can't read the error message, but my guess is that it's related.
If you change the Control Source for each Combo Box (in its Data tab) to the relevant field in Table1, that should fix it.
Just to be clear, the form Record Source is 'Table1' and the Control Source is 'WelderX' (where 'X' is A, B, C or D). The Combo Box Row Source is the query string as previously explained.
The screen resolution is too small to read clearly, but I can see your Combo Boxes are shown as 'Unbound'. I can't read the error message, but my guess is that it's related.
If you change the Control Source for each Combo Box (in its Data tab) to the relevant field in Table1, that should fix it.
Just to be clear, the form Record Source is 'Table1' and the Control Source is 'WelderX' (where 'X' is A, B, C or D). The Combo Box Row Source is the query string as previously explained.
Dear Roku,
it work pefect i just was making the code in separate modul & i shall put it in from3 modul. really i appreciate your effort & i will try to add other tests to the code & let you know.
after every thing work good i will post the new database for future forum user.
again many thanks & let me start testing the other field i hope i can do it with same
Dear Roku,
good day to you. again i stuck in the code the 1st & 2nd condition work perfect, but when i test the third value (JointT=X) it is giving mismatching result eventhough the resualt are the same.
also do you have idea how to search within string & compare it, in my attached example i need the VBA to test the type of materia & Welding process & compare if the value is available in column 10 & 11 in the Welder_tb
The basic logic is to test all conditions, adding errors to the message at each failure. Having completed the tests, check the message to see if it has content and display if it has. What you are doing is checking the first value and then checking the message, then the second condition and so on.
Once all validations have been completed, you should see is something like this in your message if errors occurred:
Errors:
Pipe diameter not matching
Thik not matching Pipe
In other words, all errors found are shown in a list at the end of the pass.
You appear to have some misunderstanding of the If statement structure. Look at the two examples below:
Code:
If <condition> Then <action>
is equivalent to
Code:
If <condition> Then
<action>
End If
There is no 'End If' where the <action> is on the same line as <condition>
If you want to use compound tests, you might use:
Code:
If <condition1> Then <action1> ElseIf <condition2> Then <action2> Else <action3>
which is equivalent to
Code:
If <condition1> Then
<action1>
ElseIf <condition2> Then
<action2>
Else
<action3>
End If
If there is no <action3> for the last example above, then you omit both the 'Else' and '<action3> lines.
My rule of thumb is to put a single test and action on one line, unless the line gets too long to read. As soon as I need an 'Else', I use multiple lines - this is for readability and maintenance.
In your tests, you have one or two conditions for each logical test. So here, you use:
Code:
If <condition1> Or <condition2> Then <add error to message>
And for the series of tests
Code:
If <condition1a> Or <condition1b> Then <add error text to message>
If <condition2a> Or <condition2b> Then <add error text to message>
If <condition3a> Or <condition3b> Then <add error text to message>
If <condition4> Then <add error text to message>
If <message is not empty> Then <display message>
Note that I have not used 'ElseIf' in this code block, because you want every test to be carried out for each welder.
In the above each pair of conditions will represent max/min for diameter, thickness etc. You put your actual code where I use <> brackets, if it's not obvious.
Forgot to answer your other question. That needs some work to unravel, but I can't do that today ... maybe tomorrow afternoon.
A quick look suggests you should have a table of material types with welding processes, with the combo box used for selection. I envisage the user selecting a material type in one Combo Box, which then selects only valid WPs for that material in another Combo Box.
If I interpret your table correctly, material CS can use SMAW, GTAW, SAW and SMAW-GTAW - correct?
I got a chance to look at your code again. A simple way to do what you want on the welding process etc. is to split the choices and check each in turn. The following does this:
Code:
Private Function checkWelder()
Dim strMessage As String
Dim ctl As Control
Rem set ctl to point to the active form control
Set ctl = ActiveControl
Rem check that it is a combo box and validate fields
If ctl.ControlType = acComboBox Then
Rem clear the error text
strMessage = ""
Rem check pipe diameter limits
If (Val(Me.wtPipeDiameter) < Val(Nz(ctl.Column(4), 0))) Or (Val(Me.wtPipeDiameter) > Val(Nz(ctl.Column(5), 0))) Then
strMessage = strMessage & vbCr & "Pipe diameter not matching" ' col 4 is Dia Min; col 5 is Dia Max (zero-relative columns)
End If
Rem check thickness limits
If (Val(Me.thik) < Val(Nz(ctl.Column(6), 0))) Or (Val(Me.thik) > Val(Nz(ctl.Column(7), 0))) Then
strMessage = strMessage & vbCr & "Thik not matching Pipe " ' col 6 is Thik Min; col 7 is Thik Max
End If
Rem check joint type
If (Me.JointT <> Nz(ctl.Column(8), "")) Then strMessage = strMessage & vbCr & "Joint not Matching " ' col 8 is JointT
Rem the type of material is in a comma-separated list
Dim strList() As String
Dim intList As Integer
strList = Split(Nz(ctl.Column(9), ""), ",") ' split the list at comma
For intList = 0 To UBound(strList)
If Me.TypeofMaterial = Trim(strList(intList)) Then Exit For
Next
If intList > UBound(strList) Then strMessage = strMessage & vbCr & "Incorrect matrial" ' col 9 is type of matrial
Rem the welding process is in a comma-separated list
strList = Split(Nz(ctl.Column(10), ""), ",") ' split at comma
For intList = 0 To UBound(strList)
If Me.WeldingProcess = Trim(strList(intList)) Then Exit For ' use Trim() to remove leading and trailing spaces
Next
Rem if intList is beyond the array limit, then the item was not found
If intList > UBound(strList) Then strMessage = strMessage & vbCr & "Incorrect welding process" ' not in list
Rem show message if any error field added to list
If Not strMessage = "" Then MsgBox "Errors:" & strMessage, vbCritical, "Data Error"
End If
End Function
The logic on the Split is to iterate each option until a match is found. If one of the options matches, intList will point to the matching array entry. If no match is found, intList points outside the array, so you can use this to determine the error condition.
If you create a record with all fields out of spec, you will get the message with all errors shown. When all fields are correct, the message doesn't appear.
I wonder whether the WelderX click event is the right place to do these checks? If you put the code in the form BeforeUpdate event, you can control when the record in Table1 is updated.
Dear Roku,
i really appreciate your effort for helping people. the data test work perfect & as i found defficult for sourcing data & examples in the forum i would like to attach the full example with additional VBA test which is very useful for the co-worker who is in the field of QAQC.
thanks again & i hope i will finish my database soon.
Forgot to answer your other question. That needs some work to unravel, but I can't do that today ... maybe tomorrow afternoon.
A quick look suggests you should have a table of material types with welding processes, with the combo box used for selection. I envisage the user selecting a material type in one Combo Box, which then selects only valid WPs for that material in another Combo Box.
If I interpret your table correctly, material CS can use SMAW, GTAW, SAW and SMAW-GTAW - correct?
Dear Roku,
your idea is Brilliant, i just was talking to the manager in charge i we were talking the same think.
let me set up the new table & hope you help me out for Combo Box verification.
Dear Roku,
again i face an other problem. i made little cahnge for welderTb & more requirement testing appears again.
the problem is like that i want the code to look first in the form for type material & compare with table if it is true then test the Welding process if it is true the will use the previous code, same code will test the rest of different material type.
i hope you will get my question correct i attached sample for ready referance.
New Picture (1).jpg
