Checking for records before saving Master

Okay as if that title wasn't confusing enough.  Here is the scenario.  I have a Shipping Form.  Once all the shipping information has been entered then the user goes to the Shipping Details form to enter the product data.  

This Details form has Load Lines on it for what product is on each line.  The user puts in Tag numbers for the appropriate Line number EXAMPLE:

HEADER
LINE - 1
SHAPE - PL
PIECES - 3
WEIGHT - 12000

DETAILS
TAG NUMBER 1
TAG NUMBER 2
TAG NUMBER 3

user goes to next line

HEADER
LINE -2
SHAPE - CO
PIECES - 4
WEIGHT - 14000

DETAIL
TAG NUMBER 1
TAG NUMBER 2
TAG NUMBER 3
TAG NUMBER 4


On save button I need to check to make sure each LINE number has TAGS entered before saving the record.  I have thought and though about the best way to do this but coming up empty.

In Summary:

Shipping Form - all information of shipment, customer, destination, truck info, ship date, time, etc
Shipping Details - Line information with Types of product, quantity, etc
Shipping Line Items - Tag Information, actual weight, actual pieces, etc

 Thoughts on this?  Thanks
LVL 2
pskeensAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
What is the structure of the data entry form?  Is everything Bound?
I don't see how you deal with multiple tag entries for multiple lines within a single overall shipping form as it implies that you have a continuous subform(for tags) within a continuous subform (for lines) which is impossible in Access.

pskeensAuthor Commented:
Good Point, that would definately help.  

The form is a continuous form that is bound to a TEMP Table.  See screenshot attached.  The Blue circled items are unbound controls.  When the form opens it sets the Line to 1 then looks at the table "TBL_LOAD_HED" and populates the unbound Line information controls (circled in blue) for the corresponding load line.  The "NEXT" and "PREV" buttons moves from one line to the next and requeries the Details below to get the right Tag information for that line.

 BOL-DET Screen
als315Commented:
You should have some reference to line# in table with tags. In this case you can count tags for every line# and, if count = 0, generate error message.
Best way to get quick help - upload DB with sample data.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

peter57rCommented:
I agree with als315.

It look like you just need a Dcount() command in your Save code. Something like..

If dcount("anynonnullfield", "Tagstable", "LineNumber= " & me.txtLinenumber) <> me.txtPcs then
'error- mismatch on tags and pieces- do something here
else
'ok; do nothing
end if

The Dcount() criteria may need to use more than one criteria field but I can't tell what the id field is.
pskeensAuthor Commented:
I had planned on using dCount but the problem I has was how to loop through the different line numbers.  I do have the Line Number and BOL number references in the Line table.  Here are the tables that house this information:

TBL_BOL_HED
TBL_BOL_DET
TBL_BOL_LINE

So My question is how to check line 1 for tag numbers, go to line 2 if existis and check for tags, go to line 3 if exists and check for tags, etc.  I need to do this for as many line numbers that are present in the load.  

Thanks for the help.
als315Commented:
Create query with counts of records for every line#.
In VBA you can open query as recordset and check all lines (I suppose you will have there two fields- "LineRecordCount" and "line#":
Dim rst As Recordset, i As Long
Set rst = CurrentDB.OpenRecordset("YourQueryName")
Do While not rst.EOF
 If rst!lineRecordCount > 0 then
        msgbox "Line#" & rst!Line#, VBOkOnly
        exit do
 end if
 rst.Movenext
loop
rst.Close

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
als315Commented:
Sorry!
 If rst!lineRecordCount = 0 then
pskeensAuthor Commented:
Here is the code

    Dim rs As Recordset, i As Long
    Set rs = CurrentDb.OpenRecordset("BolLineAudit")
    Do While Not rs.EOF
     If rs!recCt = 0 Then
            MsgBox "Line#" & rs!Line#, vbOKOnly
            Exit Do
     End If
     rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

and I am getting error "Too Few Parameters, expected 3"
peter57rCommented:
This seems over complicated to me.
I assume that you are entering the header data for Line1 and then the Tags for Line1.
Presumably you must then click one of the buttons.  Surely it is better to check the count at that moment and stop the user proceeding if it doesn't match, rather than entering multiple lines and multiple tags and then trying to sort it out afterwards?
pskeensAuthor Commented:
Peter,

The load lines are already created on a Load.  Once the user is ready to bill out the load all information is already there.  All they have to do is put the tag numbers on the correct load line.  What I am trying to achieve is the "IN CASE".

The IN CASE is if a load has more than one Line on it and a user puts tags on the first line and then hits save without going to the next line or checking to see if it has more than one.  I want to prevent the save from happening and let the user know that all load lines are not complete.  

Hope that explains what I am trying to achieve.  Thanks
als315Commented:
Change one string to:
MsgBox "Line#" & rs![Line#], vbOKOnly
pskeensAuthor Commented:
Solution worked however I never could get it to work using the Query.  I had to put the query string in VB for it to work correclty.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.