Solved

Checking for records before saving Master

Posted on 2011-09-15
12
221 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:pskeens
  • 5
  • 4
  • 3
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36547283
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.

0
 
LVL 2

Author Comment

by:pskeens
ID: 36547377
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
0
 
LVL 39

Expert Comment

by:als315
ID: 36547418
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36547555
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.
0
 
LVL 2

Author Comment

by:pskeens
ID: 36548482
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.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 36548899
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 39

Expert Comment

by:als315
ID: 36548908
Sorry!
 If rst!lineRecordCount = 0 then
0
 
LVL 2

Author Comment

by:pskeens
ID: 36549747
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"
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36549829
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?
0
 
LVL 2

Author Comment

by:pskeens
ID: 36549963
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
0
 
LVL 39

Expert Comment

by:als315
ID: 36550135
Change one string to:
MsgBox "Line#" & rs![Line#], vbOKOnly
0
 
LVL 2

Author Closing Comment

by:pskeens
ID: 36552992
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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now