Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Checking for records before saving Master

Posted on 2011-09-15
12
Medium Priority
?
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 40

Accepted Solution

by:
als315 earned 2000 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
 
LVL 40

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 40

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

647 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