Desparate for code help 1

[LMRollins]

I'm using the following code. It might not be the best way but it works so far. However I need to add something to it and when I try what I think is correct it bombs.

Private Sub txtPart_ID_Exit(Cancel As Integer)
If IsNull(Me.txtPart_ID) Then
DoCmd.RunMacro "mcrPartidok"
End If

If Me.txtUnitM = "LN" And IsNull(Me.txtLen) Then
DoCmd.RunMacro "mcrLinInch"
End If

If Me.txtUnitM = "SQI" And IsNull(Me.txtLen) Or IsNull(Me.txtWidth) Then
DoCmd.RunMacro "mcrSqInch"
End If
End Sub

Now what I need to add to this is the following:

If not(me.txtpart_id = dlookup("ID","tblparts&quot then
msgbox "no such part" and also put a check mark in me.cckmaster

I've tried everything that I can think of. I'm just learning code so bear with me.

 

[Junior1544]

If me.txtpart_id <> dlookup(&quot;ID&quot;,&quot;tblparts&quot then
msgbox &quot;no such part&quot;
me.cckmaster = true
end if

Just an idea...

Also, It looks like you are using a mix of macro's and code... I would suggest changing all your macro's over to code... It'll run better i think, and you will have more control over every thing...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[mymou]

Hi LMRollins

A little more information would be useful. I have almost no idea what you 'need to add'.

Anyway, it is unusual to use dlookup - without some criteria - &quot;The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value ...&quot;. Do you want a random value? Explain what you are trying to do.

I'd also imagine that txtPart_id is a field in tblParts - please explain.

Also, you dont specify where it 'bombs'.

Anyway here is some code - but im not sure its worth anything to you:

If Me.txtPart_ID = DLookup(&quot;[ID]&quot;, &quot;tblparts&quot;, &quot;[something] = &quot; & Forms![FormName]!ControlName) Then
MsgBox &quot;everything fine&quot;
Else
MsgBox &quot;no such part, cckmaster ticked&quot;, vbInformation
Me.cckMaster = True
End If

Stew

 

[LMRollins]

Sorry. Let me try explain what it is I'm trying to do. This is for an inventory sheet entry screen. I have a parts table with all of our known parts (TblParts). I have another table that contains all the entered info. On my form they have to enter the page no, line no, qty, and so on. They also have to enter the part id. What I need the above script to do is:

1. If the me.part is null, then msgbox &quot;need part id&quot;
2. If the me.unit measure of the part = LN (linear inch) and the me.length is null then msgbox &quot;requires length&quot;
3. If the me.unit measure of the part = SQI (sq inch) and the me.length or me.width is null then msgbox &quot;requires length & width&quot;
4. If the me.part id is not equal to the part id in tblparts then msgbox &quot;no matching part id&quot; and then have a check mark automatically put in me.cckmaster.

Ok. As the code is now it works fine but if I try to add the coding for item #4 listed above it starts putting checks marks in me.cckmaster for any part enter that refers to any of the coding above. The only time it doesn't put a check mark is when the part has a unit measure other than LN and SQI which is what it is supposed to do.

I hope this helps.

 

[mymou]

Hi LMRollins

Now we are getting somewhere.

You have all the validation taking place when txtPart exits. It can be done this way - or control by control.

1. If the me.part is null, then msgbox &quot;need part id&quot;
In a table or on a form - there is validation - that can be part of the properties. Look up validation Rule and Validation Text in help. This will allert the user as soon as he leaves [part].

2. If the me.unit measure of the part = LN (linear inch) and the me.length is null then msgbox &quot;requires length&quot;
You can also use expressions in Validation Rule. Unfortunately - cannot use user defined functions!! You must use an event on the [unit] control to trigger validation. I would reccommend you use the Before Update event so you have the option of undoing the changes in the data (if desired). The code you use already will do the trick if it is already working.

If Me.txtUnitM = &quot;LN&quot; And IsNull(Me.txtLen) Then
DoCmd.RunMacro &quot;mcrLinInch&quot;
End If
If Me.txtUnitM = &quot;SQI&quot; And IsNull(Me.txtLen) Or IsNull(Me.txtWidth) Then
DoCmd.RunMacro &quot;mcrSqInch&quot;
End If

This will alert the user as soon as they leave the [unit] control.

3. If the me.unit measure of the part = SQI (sq inch) and the me.length or me.width is null then msgbox &quot;requires length & width&quot;
see above

4. If the me.part id is not equal to the part id in tblparts then msgbox &quot;no matching part id&quot; and then have a check mark automatically put in me.cckmaster.
Why not use a combobox (with tblparts as the recordsource) - with a restricted list - to get these values. Then no mistake can be made.

Changing the macros to code is a good idea - and is easily done - TOOLS/MACROS/CONVERT.

Think that is all that i can do - you should do a keyword search on 'validation' (maybe on the access forms forum) to get a good idea of the issues involved in validation. I am certainly no expert in this area.

Good luck.

Stew

 

[LMRollins]

All good suggestions but here are the reasons I can't do these as you suggest.

1. I would like my own shortened msgbox.

4. I can't use a restricted parts table because sometimes we have parts that are no longer listed in this table. That is why I want it to put the checkmark. That way they can run a report and see which parts are no longer in the list.

 

[LMRollins]

I finally got it to work. I had to put 2 & 3 as an event on a different field. Once I did that it works great. I guess I was trying to do too many things in one event and it just couldn't handle it.

Thank you so much for your help. It was actually your suggestion that I put something on another event that helped me out.

Thanks
Lori

 

[mymou]

Hi LMRollins

1. I would like my own shortened msgbox.
Not sure what you mean here - but anyway - the point was that maybe you should consider putting validation on appropiate fields on 'before update'(using VBA, macros or Validation rules - does really matter (but more control with VBA)).

4. I can't use a restricted parts table because sometimes we have parts that are no longer listed in this table. That is why I want it to put the checkmark. That way they can run a report and see which parts are no longer in the list.
A combobox will still work for you here. Set the property 'limit to list' to No.
Don't like the way this sounds - a parts table that doesn't have the parts in it - but never mind.

If (DCount(&quot;[ID]&quot;, &quot;tblparts&quot;, &quot;[part?] = &quot; & Me.txtPart_ID?)>=1 Then
MsgBox &quot;everything fine&quot;
Else
MsgBox &quot;no such part, cckmaster ticked&quot;, vbInformation
Me.cckMaster = True
End If

Does this work - havent tried it - but i think it should do the job.

Stew