pskeens
asked on
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
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
ASKER
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.
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.
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.
Best way to get quick help - upload DB with sample data.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry!
If rst!lineRecordCount = 0 then
If rst!lineRecordCount = 0 then
ASKER
Here is the code
Dim rs As Recordset, i As Long
Set rs = CurrentDb.OpenRecordset("B olLineAudi t")
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"
Dim rs As Recordset, i As Long
Set rs = CurrentDb.OpenRecordset("B
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"
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?
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?
ASKER
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
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
Change one string to:
MsgBox "Line#" & rs![Line#], vbOKOnly
MsgBox "Line#" & rs![Line#], vbOKOnly
ASKER
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.
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.