loop through table records (1 Viewer)

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79
Gentlemen, good day
i need your help regarding attached database
i have 3 tables

1 - invoice lines
2 - store available loots ( every loot has its own cost)
3 - invoice usage material

when i click invoice post button , i need loop event , to create records in table (invoice usage materials ) for each invoice line , go to first batch record and create line with the quantity available in the batch , and if the quantity was not enough go to next batch line ... ets

I really appreciate your kind help because all my warehouse work depending on this kind of transactions
 

Attachments

  • Loop required.accdb
    1.1 MB · Views: 111

vba_php

Forum Troll
Local time
Today, 03:27
Joined
Oct 6, 2019
Messages
2,884
you probably won't find anyone to do this for you my friend, but here's something to get you started:
Code:
Option Compare Database

Private Sub Command4_Click()
    Dim db As dao.Database
    Dim rs_invoiceline As dao.Recordset
    Dim rs_usage As dao.Recordset
    Dim rs_batch As dao.Recordset
    
    Set db = CurrentDb
    Set rs_invoiceline = db.OpenRecordset("tbl_invoiceline")
    Set rs_usage = db.OpenRecordset("tbl_salesusagematerial ")
    Set rs_batch = db.OpenRecordset("tbl_batch")
    
        With rs_invoiceline
            .MoveLast
            .MoveFirst
                Do Until .EOF
                    'read the field data here, do what you want with it
                    'use the code ".fields("FIELD_NAME_HERE")" to read the field data
                    
                    'if your above condition is not met, do what you want with the data in the table "tbl_batch"
                    If [your condition is false] Then
                        rs_batch.MoveLast
                        rs_batch.MoveFirst
                            'read the fields of the records in "tbl_batch here if you need to, using the code above
                            'and, if you need to add records "tbl_batch", use "rs_batch" and the .ADDNEW method
                    End If
                        .MoveNext
                Loop
        End With
           
    rs_invoiceline.Close
    rs_usage.Close
    rs_batch.Close
           
Set rs_invoiceline = Nothing
Set rs_usage = Nothing
Set rs_batch = nothing
    
End Sub
so try it out, google the rest of it and if you get stuck, we can give you a boost.
 
Last edited:

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79
you probably won't find anyone to do this for you my friend, but here's something to get you started:
Code:
Option Compare Database

Private Sub Command4_Click()
    Dim db As dao.Database
    Dim rs_invoiceline As dao.Recordset
    Dim rs_usage As dao.Recordset
    Dim rs_batch As dao.Recordset
    
    Set db = CurrentDb
    Set rs_invoiceline = db.OpenRecordset("tbl_invoiceline")
    Set rs_usage = db.OpenRecordset("tbl_salesusagematerial ")
    Set rs_batch = db.OpenRecordset("tbl_batch")
    
        With rs_invoiceline
            .MoveLast
            .MoveFirst
                Do Until .EOF
                    'read the field data here, do what you want with it
                    'use the code ".fields("FIELD_NAME_HERE")" to read the field data
                    
                    'if your above condition is not met, do what you want with the data in the table "tbl_batch"
                    If [your condition is false] Then
                        rs_batch.MoveLast
                        rs_batch.MoveFirst
                            'read the fields of the records in "tbl_batch here if you need to, using the code above
                            'and, if you need to add records "tbl_batch", use "rs_batch" and the .ADDNEW method
                    End If
                        .MoveNext
                Loop
        End With
           
    rs_invoiceline.Close
    rs_usage.Close
    rs_batch.Close
           
Set rs_invoiceline = Nothing
Set rs_usage = Nothing
Set rs_batch = nothing
    
End Sub
so try it out, google the rest of it and if you get stuck, we can give you a boost.


boss really i tried but i cannot , iam not that well knowing with vba , i used to do it manually before entering to company system , but it takes a lot of time especially if iam need to issue 100 items in one invoice
 

vba_php

Forum Troll
Local time
Today, 03:27
Joined
Oct 6, 2019
Messages
2,884
boss really i tried but i cannot , iam not that well knowing with vba , i used to do it manually before entering to company system , but it takes a lot of time especially if iam need to issue 100 items in one invoice
Ahmed,

Colin just showed me that you cross posted this. I *never* check for cross posting as I don't have the time to do that, but if you want my help then delete the thread you started on Utter Access because more than likely you will end up getting 2 people to help you at once and that annoys almost anyone. thanks.
 

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79
Ahmed,

Colin just showed me that you cross posted this. I *never* check for cross posting as I don't have the time to do that, but if you want my help then delete the thread you started on Utter Access because more than likely you will end up getting 2 people to help you at once and that annoys almost anyone. thanks.

Mr. Adam , i never intended to waste time of you , but i really want help in this issue to solve , i try to delete from Utter Access but i do not know how to delete it i can not find delete button :D , iam not a vba-programmer iam just access beginner , i just inventory accountant and i got enough of using excel
 

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79

vba_php

Forum Troll
Local time
Today, 03:27
Joined
Oct 6, 2019
Messages
2,884
boss , I'm not manipulating i just want to get help , and because i am in hurry to solve it i just post it there in utter access , i have no knowledge of "crossposting " , :banghead:
i'm not your boss. that's the guy that hired you. ;) i created a test thread no the other forum to see if one can be deleted. there doesn't seem to be an option. so if you don't post anymore over there, I'll continue to help. did you even attempt to add to the code i gave u? and by the way, saying "i'm in a hurry" doesn't help. the folks here who give answers sometimes are just busy, and that's something you have to accept. but a lot of times, they're quick at responding. and take a look at the attached image to this post! why ask a question here when utter access seems to be very good at itemizing the issues and pinpointing where you can ask questions? this forum doesn't have that. but from what I hear, we still get more traffic than utter access does...
 

Attachments

  • utter access menu options.jpg
    utter access menu options.jpg
    99.9 KB · Views: 108

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79
i'm not your boss. that's the guy that hired you. ;) i created a test thread no the other forum to see if one can be deleted. there doesn't seem to be an option. so if you don't post anymore over there, I'll continue to help. did you even attempt to add to the code i gave u? and by the way, saying "i'm in a hurry" doesn't help. the folks here who give answers sometimes are just busy, and that's something you have to accept. but a lot of times, they're quick at responding.

i can be a loyal servant for the man who give me benefit information, because i appreciate the people who have the knowledge,:)

i will not post there "utter access"any more ,:banghead:
and i'm not in hurry :eek:
and i really tried to to work with the code but my knowledge with access vba is copy and past code :rolleyes:

will you help me now ?:D
 

vba_php

Forum Troll
Local time
Today, 03:27
Joined
Oct 6, 2019
Messages
2,884
will you help me now ?:D
sure, but your initial explanation is really confusing. can you explain what you need any better than that? I might be able to decipher it if I read thru it again carefully, but can you offer us anything more in terms of it being understandable? hopefully others here will join in this discussion. I'm headed home at the moment, but if no one else chimes in, I'll check your thread later on again.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:27
Joined
Sep 21, 2011
Messages
14,037
Mr. Adam , i never intended to waste time of you , but i really want help in this issue to solve , i try to delete from Utter Access but i do not know how to delete it i can not find delete button :D , iam not a vba-programmer iam just access beginner , i just inventory accountant and i got enough of using excel

You do not have to delete your post. Just have the courtesy to mention that you have posted the same question in other forums with a link to that thread as isladogs has done.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:27
Joined
May 7, 2009
Messages
19,169
I have not used item_description on tbl_salesusagematerial.
added batch_used field on tbl_batch to monitor how many have actually been used on each batch.
plz. see the code behind the form.
 

Attachments

  • Loop required.accdb
    604 KB · Views: 127

cheekybuddha

AWF VIP
Local time
Today, 08:27
Joined
Jul 21, 2014
Messages
2,237
@vba_php

>> i created a test thread no the other forum to see if one can be deleted. there doesn't seem to be an option. <<

I have deleted your test thread on UA for you.

I have left the OP's original thread because it might be of interest to other users in the future

d
 

SjCc

Registered User.
Local time
Today, 01:27
Joined
Oct 5, 2017
Messages
67
I have not used item_description on tbl_salesusagematerial.
added batch_used field on tbl_batch to monitor how many have actually been used on each batch.
plz. see the code behind the form.

Hi ArnelGp here is link waiting for your assistance too
 

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 01:27
Joined
Feb 25, 2015
Messages
79
I have not used item_description on tbl_salesusagematerial.
added batch_used field on tbl_batch to monitor how many have actually been used on each batch.
plz. see the code behind the form.


Mr. ArnelGp

this is attached Picture for what i wanted to do in my sample access database
and before i publish you just wrote the code , :eek:

really Iam very happy to see the result :eek::

really thank you , and thanks to every one reply to my post or even take a look:):):):):)
 

Attachments

  • Untitled.png
    Untitled.png
    77.5 KB · Views: 113

Users who are viewing this thread

Top Bottom