none
Populate a form with a record no. according to another linked field RRS feed

  • Question

  • Hi there!

    I sort of have a small but little complicated-like issue which I need to resolve before Saturday night AEST as this is a project (student project). I am building a fitness database and I created a table "Customers" and it has a field called "Customer_ID" which is the primary key. I also created a table "Customer_Sign_INs" and created the same "Customer_ID" field and set the lookup in this sign in table to get Customer IDs from Customer's Table & created a relationship linking these two. So I made a Sign In Form which the customer inputs his Customer ID (I call it a Member ID for them and it allows them to scan their card and input their unique ID) and if the ID is correct and matches with the card, they get access.

    My problem occurs here, I created a Customer Home Menu where they can do all sorts of things, in there there is a option which allows customers to modify their details. What I wanna do is whenever someone clicks that button, the command should basically allow the signed in customer to modify their details which how I had planned was, when that is clicked, access will go to the "Customer_Sign_INs" table, go in the "Customer_ID" field on that able and get the last Customer ID entered (which is basically the last sign in). Then it would open the Customers Form which allows editing and it should AUTOMATICALLY take the Customer ID it got from Customer_Scan_INs and look for the record with the same ID in the "Customers" table and auto populate that customers form so a personal can really modify only his own details, not someone else's.

    It's confusing to explain and therefore I got some pictures with easier explanation;

    Customer Table: http://imgur.com/a/VNekU

    Customer_Sign_INs Table: http://imgur.com/a/rBDLT

    Customers Form: http://imgur.com/a/1ttYr



    • Edited by harshsyd Friday, September 23, 2016 3:31 AM
    Friday, September 23, 2016 3:21 AM

Answers

  • Hi,

    You can open a form filtered to the same CustomerID by using the WhereCondition argument of the OpenForm method. For example,

    DoCmd.OpenForm "FormName", , , "FieldName=" & Me.FieldName

    Hope it helps...

    • Marked as answer by harshsyd Friday, September 23, 2016 5:37 AM
    Friday, September 23, 2016 3:40 AM

All replies

  • Hi,

    You can open a form filtered to the same CustomerID by using the WhereCondition argument of the OpenForm method. For example,

    DoCmd.OpenForm "FormName", , , "FieldName=" & Me.FieldName

    Hope it helps...

    • Marked as answer by harshsyd Friday, September 23, 2016 5:37 AM
    Friday, September 23, 2016 3:40 AM
  • Hi! I looked into your reply but I am a little confused as I'm pretty much a beginner. I did this in Macros with the OpenForm Action in the Where Condition field. But I think this solution works with VBA code right?

    So, for the command, I entered this in the WhereCondition: "Do.Cmd.OpenForm "(C) Customers", "FieldName=Customer_ID" &Me.FieldName . But I got errors for that and I am kind of confused with the statement. In the "FormName" I'm pretty sure it's the Customers Form right? FieldName should be Customer_ID right?

    One thing, why are there three commas in your statement before "FieldName="? Is that supposed to be there or? I tried with and without three commas, shouldn't one comma be it?

    With what I entered; this is that I get: http://imgur.com/a/po5PY

    Sorry if I am doing something totally stupid, I'm not an Access champion, I'm just a very beginner. Also I'm doing this with Macros, so is this a method for Macros or a VBA Code?

    Is it possible if you could provide me a little detail on how that statement works and where would I put my fields and all.

    Thanks!



    • Edited by harshsyd Friday, September 23, 2016 4:44 AM
    Friday, September 23, 2016 4:39 AM
  • Hi,

    If you're using macros, then you just need to provide the arguments to the OpenForm action. For example:

    FormName: Customers

    WhereCondition: "[Customer ID]=" & Forms![Customer Home Menu].[Customer ID]

    Hope it helps...

    Friday, September 23, 2016 4:45 AM
  • Hi! I tried the VBA Code and it worked.

    I am nearly there but this time one thing is changed. Firstly, I disabled the macro for that button and entered this: (DoCmd.OpenForm "(C) Customers", , , "FieldName = Customer_ID") [without brackets obviously].

    Now when I click the command, a new small window/box appears and it says FieldName and has a textbox below.

    When I enter the Customer ID in that textbox it takes me to the customer's form and that customer id record. Which is what I wanted.

    But how can I change the little text on top of the textbox which says "FieldName"? I'm fine with the person re-inputting their ID once more to modify their details, but how am I able to change the box saying "FieldName".

    Thanks for your time! One last thing!


    • Edited by harshsyd Friday, September 23, 2016 5:12 AM
    Friday, September 23, 2016 5:12 AM
  • Hi,

    Glad to hear you're making good progress. Replace "FieldName" with the actual name of your field (Customer ID?) and move the "Customer_ID" part outside of the argument, like I showed earlier:

    "[Customer ID]=" & Me.[Customer ID]

    Hope it helps...

    Friday, September 23, 2016 5:32 AM
  • Hi!

    Thanks mate for your help! And I know I was kind of dodgy and 'stupid' with this but in the end it finally worked out.

    Thanks for your time & help! Answer Marked! Thank you!

    Friday, September 23, 2016 5:37 AM