Results 1 to 12 of 12

Check if both subforms have the same reference number then copy a value to a given field

  1. #1
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    Check if both subforms have the same reference number then copy a value to a given field

    Hello Forum,


    I’ll try tomake my question simple…
    The purpose of the DB I am trying to build it to issue statements to agents.

    I have a main form – FmPrincipal - with two subforms – SfmCommissions and SfmExpenses -each related to two tables : TbCommissions and TbExpenses.


    These tables have 3 fields in common (plus their own fields): Name, Reference, StatementDate.The table Expenses has, among others, a field ReimbDate.

    The user enters data in the subforms as needed: sometimes there are only commissions toenter, sometimes only expenses, sometimes both. In the last case, the userenters the same Reference number in both subforms so the statement will showboth commissions and expenses.

    When the user enters their data in the subforms and clicks the Validate button, it triggers a dialog form (FmTbReleve) which asks for the date to be shown on the Statement.Then, the Ok button of the dialog form does this:

    Code:
    Private SubBtnOkReleve_Click() DoCmd.RunSQL"INSERT INTO TbReleve ( NomCourtier, DateRel, NoRel )SELECT[Formulaires]![FmTbReleve]![TxNoCourtier] AS NomCourtier,[Formulaires]![FmTbReleve]![TxDateRel] AS DateRel,[Formulaires]![FmTbReleve]![TxNoRel] AS NoRel;" DoCmd.RunSQL"UPDATE RqCommission SET RqCommission.DateReleve =[Formulaires]![FmTbReleve]![TxDateRel] WHERE(((RqCommission.NomCourtierC)=[Formulaires]![FmTbReleve]![TxNoCourtier]) AND(RqCommission.RefReleve)=[Formulaires]![FmTbReleve]![TxNoRel]);" DoCmd.RunSQL"UPDATE RqDepense SET RqDepense.DateFact =[Formulaires]![FmTbReleve]![TxDateRel] WHERE(((RqDepense.NomCourtierD)=[Formulaires]![FmTbReleve]![TxNoCourtier]) AND(RqDepense.RefReleve)=[Formulaires]![FmTbReleve]![TxNoRel]);" IfMe.DepFixe = True Then DoCmd.RunSQL"INSERT INTO TbDepense ( NomCourtierD, RefReleve, TypeDepense, Ecriture,Montant, TPS, TVQ, Exempte, DateFact )SELECT[Formulaires]![FmTbReleve]![TxNoCourtier] AS NomCourtierD,[Formulaires]![FmTbReleve]![TxNoRel] AS RefReleve, 'FLP Exchange' asTypeDepense, 'Facture' as Ecriture, 0 as Montant, 0.05 as TPS, 0.09975 as TVQ,False as Exempte, [Formulaires]![FmTbReleve]![TxDateRel] AS DateFact;" DoCmd.RunSQL"INSERT INTO TbDepense ( NomCourtierD, RefReleve, TypeDepense, Ecriture,Montant, TPS, TVQ, Exempte, DateFact )SELECT[Formulaires]![FmTbReleve]![TxNoCourtier] AS NomCourtierD,[Formulaires]![FmTbReleve]![TxNoRel] AS RefReleve, 'Marketing CHD' asTypeDepense, 'Facture' as Ecriture, 0 as Montant, 0.05 as TPS, 0.09975 as TVQ,False as Exempte, [Formulaires]![FmTbReleve]![TxDateRel] AS DateFact;" End If DoCmd.Close End Sub

    Here iswhat I need: IF there are both commissions and expenses on the same statement – same reference number for the same agent – then the StatementDate (entered in the dialog form) should automatically be copied into the ReimbDate.

    I Don't know enough in VBA to write this code, unfortunately
    and I hope somebody will be able to help me with this one.

    Have a good day all and thanks in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    A few notes first:
    using reserved words is not a great idea NAME is a reserved word in MS access and may cause problems for you in the long run
    if your data types for NAME and REFERENCE are hand entered values, you are asking for problems. the links between your tables should be values that do not change and are unique. For instance if NAME is the name of a person you should have a table dedicated to your staff/users

    Code:
    tblEmployee
    E_ID  E_lastname E_firstname -----> other employee related fields
    then store the employee id (E_ID in this example) in your NAME field rather than the text value of the name.

    Finally it's going to be hard to give you direction because there is not enough information in your post. I would suggest posting a sample of your database with enough junk data in it to create the problem you're trying to solve. If it's a data entry issue then give instructions on what to enter and what you want to have happen with screen shots etc.

  3. #3
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Good morning rpeare and thank for trying to help me.

    Please find attached a dummy database. First thing you need to know is that I work with a French version of Access. Therefore, you may (or may not!) encounter troubles with the queries.

    In the main form:
    Select an agent in the drop down list.
    In both subforms (in the main form called "FmPrincipal"), enter a number above 10 in any row, in the "#" field. That means you are going to generate a statement number 11 (for example), showing both commissions and expenses.
    Then click on button number 3 "Generate statem.". Fill in the statement number (11) and then the date. You can ignore the checkbox.

    That's where I need help. When you click Ok, there is a code with SQL language to insert a new record in my Statement table and to update records in my Commission table and my Expense table. That is where I would also like to have a code that copies the statement date in the DateRemb field in my Expense table/Form, ONLY IF I just entered the same statement number for the same name in both tables. That will prevent users from entering this date manually and make mistakes, and that will allow me to create my query calculating the balance due by the agent.

    Should you have any question, please Don't hesitate. I would really like to avoid this manual entry.

    Thank you again!

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    I took a look and concluded that you've made this more difficult by using datasheet subforms with multiple similar records. If it was one item that requires commissions and/or expenses I could probably come up with something without too much difficulty. Because there are multiple records that can require commissions and/or expenses, the problem becomes picking those out of a list of records. One cannot use the current (active) record property on either of the 2 subforms, so some sort of flag would be required, such as a checkbox that will denote which of the many records will need to be checked for the values you want to check. However, that would require you to switch from datasheet to a continuous form. Nor can you use a DLookup on either of the 2 tables using any of the datasheet records because there's no way to decide which one(s) to use.

    I'm not saying it's impossible with the setup you have; just that the level of work and the language difference is such that it transcends a simple help forum task and becomes something worthy of paid development. I see having to loop through your datasheet records on each form, looking for which records have RefReleve values, and using the ID values for those records to look for matches in your table. Maybe someone else will have a better idea than me and can also easily work with the French terms.
    Sorry I could not be of more assistance.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,333
    I looked at your database briefly.
    I tried to follow your instructions, but the code received an error (field not found or similar).

    I used Marlene as the test, number was 21 and date was 11-Aug-18.

    I then adjusted the code as below ; stepped thru and this adjusted line did not error. This was just to get the code to work.


    Code:
    DoCmd.RunSQL "INSERT INTO TbReleve ( NomCourtier, DateRel, NoRel )" _
    & "SELECT '" & Me.[TxNoCourtier] & "' AS NomCourtier,#" _
    & Me.[TxDateRel] & "# AS DateRel," _
    & Me.[TxNoRel] & "  AS NoRel;"
    Single quote around a text value in quoted string; # surrounding dates; numbers do not require special prefix/suffix.
    You have to get the value from those variables and use them in the sql. Since the button is on the form, you can use the Me notation.

    I recommend setting up your sql in variables, this allows you to do Debug.Print of th sql to see how Access will render the statement.

    Dim SQL1 as string
    SQL1 = "INSERT INTO TbReleve ( NomCourtier, DateRel, NoRel )" _
    & "SELECT '" & Me.[TxNoCourtier] & "' AS NomCourtier,#" _
    & Me.[TxDateRel] & "# AS DateRel," _
    & Me.[TxNoRel] & " AS NoRel;"

    Debug.print SQL1

    I suggest you review what exactly you need and seek options that are feasible as Micron said.

  6. #6
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Thank you very much Micron and Orange for your suggestions.

    Micron, I don't know that I made things more difficult. On one side I have commissions to pay, on the other side I have expense reimbursements to receive. The statements may show both of them, depending on the monthly activity. The person who is going to use the DB wants both on the same statement + the balance and that is why I came up with this design.

    Orange, I will try your code for sure and get back to you to let you know if everything went well.

    While you were both looking at my problem, I kept working on it and created the following query:

    Code:
    UPDATE TbDepense INNER JOIN TbCommission ON (TbDepense.RefReleve = TbCommission.RefReleve) AND (TbDepense.NomCourtierD = TbCommission.NomCourtierC) SET TbDepense.DateRemb = [TbDepense]![DateFact]
    WHERE (((TbDepense.NomCourtierD)=[TbCommission]![NomCourtierC]) AND ((TbDepense.RefReleve)=[TbCommission]![RefReleve]) AND ((TbDepense.DateFact) Is Not Null));
    It does the job, but works on all the records, not the one(s) to which I just added my statement number (I get the Access warning that XX records are going to be updated, yes or no…). I added it to my Ok button with the rest of the code.
    I am wondering if I could insert something somewhere in this query to make it update only the record(s) whose reference number is the highest, maybe with the "max" thing, knowing that each time a statement is issued, the reference number will increase…

    Have a good day/afternoon/evening all.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    Micron, I don't know that I made things more difficult.
    If that were really the case, you wouldn't be saying
    It does the job, but works on all the records, not the one(s) to which I just added my statement number
    That's exactly what I stated - you have no way to differentiate between one record or another when you want to create records that are associated with your subform records. Usually if you want to add a record (expense or whatever) you associate it with something and present that "something" in a form header and add expense/commission records to it. You have buried your "something" data as part of the commission/expense records which is difficult enough. That you are trying to distinguish between many e/c records to do something to just certain ones in the list makes it worse.

    I would have approached this as the "one" entity being on the form header and the e/c records in subforms below. Can't say too much more than that because given the French, I never determined if the "one" is a person/task/work order or what. However if possible I would have had a situation where I either add one expense or one commission record per subform, and if it was to edit, either filter the subs to that one record or at least have someone choose one and pass that to a single record editing form. That's just my opinion and you don't have to agree, but I smile when you say you didn't make it difficult yet here you are trying to figure it out.

  8. #8
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Good morning Micron,

    Sorry I was out for a while...
    I am smiling too reading your reply (if not laughing out loud)

    May I give you a "hint" that I forgot to mention at the very beginning, which I really apologize for: The data you can see in the expense and commission tables are existing records that were processed before and that I am using for my tests (I just deleted the statement numbers and the dates in some cases). In reality, the person using the DB will enter the information as they need to issue statements, maybe once or twice a month for each agent. Therefore there won't be any "pre-entered" rows in the tables. I Don't know if this new information changes anything…?

    As for the different approach, you are making me think a lot but I haven't found anything easier yet… I wish I could explain the whole process and the requirements more easily and be perfectly fluent in English , so you could understand why I built the DB this way.

    Thank you anyway to everyone for your help and the time you spent on my question. And Micron, should I finally find a simpler way for this DB, I will let you know


  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,333
    Rose,
    Your English seems more than adequate for communications. However, if you have a detailed description to provide to readers, then I suggest you write that description/requirement in your native language and use Google translate to put it into English for posting.
    I recognize that details in mother tongue are not easily communicated in an alternate language unless you are a linguist or have studied or lived among the second language community.

    Sometimes, the rationale for a particular design or approach can help orient the reader.

    Good luck with your project.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    Hopefully it was understood that the language difficulty I referred to is about trying ro follow what is related to what in the database. Your command of English in the forum is superb and I didn't mean ro imply otherwise.

    Orange has a good idea. If you have the time to provide a synopsis of the requirements hopefully we can reward that with some focused help. And yes, what you said about the records would probably change the way they are presented thus changing my point of view.

    Be warned that if you write that synopsis we will likely have questions because there is a slim chance that you will think of everything.
    Good luck!

  11. #11
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    No worry Micron, I understood very well that you were talking about the DB, not my language.
    I will follow Orange's advice and take the time to write a detailed description of the ins and outs and have Google translate it (crossing my finger that there won't be anything offensive in the translation ).

    I am wondering though, should I open a new thread in the "Database Design" forum instead?

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    Quote Originally Posted by Seiquo View Post
    I am wondering though, should I open a new thread in the "Database Design" forum instead?
    Probably a good idea to start fresh. If you think there is something in this thread that is relevant to the new one, you can also include a link to this one.
    If you're not already using translation for your posts, you probably don't need it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Check Box Copy Data
    By cmorton in forum Access
    Replies: 2
    Last Post: 03-29-2016, 04:28 AM
  2. Replies: 16
    Last Post: 03-26-2013, 07:11 PM
  3. Check if record exists, Check Number
    By burrina in forum Forms
    Replies: 9
    Last Post: 01-06-2013, 03:49 PM
  4. Auto generate reference number
    By JonB1 in forum Import/Export Data
    Replies: 1
    Last Post: 02-19-2011, 06:38 AM
  5. Correspondence Reference Number Generation
    By ildanach in forum Forms
    Replies: 1
    Last Post: 04-28-2009, 12:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums