Solved

Checking for records before saving Master

Posted on 2011-09-15
12
220 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 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

14 Experts available now in Live!

Get 1:1 Help Now