Results 1 to 11 of 11
  1. #1
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70

    Form retrieve data from table.

    Hi All,



    I have form A and form B, once i have submit the form A all data will go into table.
    Form A and form B is link with a SCAR number.
    When i'm in form B, i wish to have a back button. Once i click the back button, it will go back to form A and we can see the information (pull information from table).
    Any idea how to do this?

    Click image for larger version. 

Name:	form A.JPG 
Views:	36 
Size:	34.7 KB 
ID:	41284Click image for larger version. 

Name:	form b.JPG 
Views:	36 
Size:	33.2 KB 
ID:	41285

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,997
    to go to ANY form:

    docmd.openform "fMyForm"

    so each button would call the other form.
    to open it on 1 key value:

    (numeric)
    docmd.openform "fMyForm",,,"[scar]=" & txtScarBox


    (string)
    docmd.openform "fMyForm",,,"[scar]='" & txtScarBox & "'"

  3. #3
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70
    Quote Originally Posted by ranman256 View Post
    to go to ANY form:

    docmd.openform "fMyForm"

    so each button would call the other form.
    to open it on 1 key value:

    (numeric)
    docmd.openform "fMyForm",,,"[scar]=" & txtScarBox


    (string)
    docmd.openform "fMyForm",,,"[scar]='" & txtScarBox & "'"
    Hi Ranman,

    Thank for your reply. But my objective is to pull the data from table.
    I'm having difficulty to pull the data for the SCAR number.
    How can i command the program to pull only particular SCAR# ?

    FOR rs!SCAR = ME.TXTSCAR ...... THEN ...... ? AM I RIGHT?

    Click image for larger version. 

Name:	table1.JPG 
Views:	34 
Size:	49.4 KB 
ID:	41286

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,164
    Did you try Ranman's code in the Back button of your Form B? It will do exactly what you want.
    docmd.openform "Form A",,,"[scar]='" & txtScarBox & "'" 'replace Form A with your actual name

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    54
    Thank for your reply. But my objective is to pull the data from table.
    I'm having difficulty to pull the data for the SCAR number.
    How can i command the program to pull only particular SCAR# ?

    FOR rs!SCAR = ME.TXTSCAR ...... THEN ...... ? AM I RIGHT?
    I agree that the above suggested solutions sound good. And the answer to this specific question is that each of the forms would be bound to the table by their RecordSource property being the name of the table.

  6. #6
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70
    Quote Originally Posted by Gicu View Post
    Did you try Ranman's code in the Back button of your Form B? It will do exactly what you want.
    docmd.openform "Form A",,,"[scar]='" & txtScarBox & "'" 'replace Form A with your actual name

    Cheers,
    Hi Gicu,

    I've tried Ranman's code. But his coding the coding is only open Form A and input the form A text Scar number.
    In form A, there are many column, how the program knows it only pull out for that particular scar number from the table?

    For example

    Scar number | Issue |
    1 | Flat tyre |
    2 | Broken mirror

    In form B, i clicked back, then it will automatically fill up scar number 1.
    But the program doesn't know which ISSUE they need to select... Whether is flat tyre of broken mirror.

    So my coding need to be, Select scar number 1 and issue is flat tyre.
    I tried to use For scar number 1 ..... then.... but it has errors.
    Any idea use what coding?

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,164
    The scar number should be unique so issue flat tyre would always be associated with scar # 1 in your example. If you would have:

    Scar|Issue
    1 |Flat tyre
    1 |Broken mirror

    then you could use "Form A",,,"[scar]='" & Me.txtScarBox & "' AND [Issue] = '" & Me.txtIssue & "'" where Me.txtIssue is a text box on form B holding the issue. Or much better use the unique primary key of the table to do the navigation if it is different then the scar #.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70
    Quote Originally Posted by Gicu View Post
    The scar number should be unique so issue flat tyre would always be associated with scar # 1 in your example. If you would have:

    Scar|Issue
    1 |Flat tyre
    1 |Broken mirror

    then you could use "Form A",,,"[scar]='" & Me.txtScarBox & "' AND [Issue] = '" & Me.txtIssue & "'" where Me.txtIssue is a text box on form B holding the issue. Or much better use the unique primary key of the table to do the navigation if it is different then the scar #.

    Cheers,
    Vlad
    Hi Gicu,

    Yes, my scar number is unique number.
    The problem i'm facing is once i click "back", it only shown the SCAR number but all the content not showing up.
    My objective is when i pull the unique SCAR number, then the form column need to fill up with the content.

    Click image for larger version. 

Name:	Back.JPG 
Views:	18 
Size:	23.5 KB 
ID:	41398

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    196
    I am thinking the form is unbound therefore if my thinking is correct it will not pull the rest of the information. I would create a query to find the scar# then input the info back into the table from the query using a recordset. I may be wrong as i am still learning this myself.

    i'm thinking something like this:

    Code:
    docmd.openform "fMyFormA"
    
    'Sets table "tbl_YourDataTable" as recordset
    Set dbTest = CurrentDb
    Set rstTestData = dbTest.OpenRecordset("tbl_YourDataTable")
    
    With rstTestData
         IF rstTestData!Scar = Forms!FormB!txtboxwithscar.value then
         txtArea = rstTestData!Area ' change txtArea with your control name for the area textbox and the Area after rstTestData! is your field name 
         txtStatement = rstTestData!Statement
         'keep going for all the items you want to have data for
         End If
    End With
    Set rstTestData = Nothing
    Last edited by NightWalker; 03-26-2020 at 04:32 PM.

  10. #10
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70
    Quote Originally Posted by NightWalker View Post
    I am thinking the form is unbound therefore if my thinking is correct it will not pull the rest of the information. I would create a query to find the scar# then input the info back into the table from the query using a recordset. I may be wrong as i am still learning this myself.

    i'm thinking something like this:

    Code:
    docmd.openform "fMyFormA"
    
    'Sets table "tbl_YourDataTable" as recordset
    Set dbTest = CurrentDb
    Set rstTestData = dbTest.OpenRecordset("tbl_YourDataTable")
    
    With rstTestData
         IF rstTestData!Scar = Forms!FormB!txtboxwithscar.value then
         txtArea = rstTestData!Area ' change txtArea with your control name for the area textbox and the Area after rstTestData! is your field name 
         txtStatement = rstTestData!Statement
         'keep going for all the items you want to have data for
         End If
    End With
    Set rstTestData = Nothing
    Hi All,

    I thought i've solved the issue with Dlookup.

    But i'm not sure why it always show up the data in the table from the 1st row.

    Code:
    Private Sub btnback_Click()
    
    DoCmd.OpenForm ("F1NCForm")
    
    
    Forms!F1NCform!txtscar = Forms!F2CAPAform!txtscar
    
    
    Forms!F1NCform!txtarea = DLookup("Area", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtstatement = DLookup("Statement", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtreq = DLookup("Requirement", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtoe = DLookup("Objective_Evidence", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtlink = DLookup("Hyperlink", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtoe = DLookup("Objective_Evidence", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!cmbdept = DLookup("Assigned_to_dept", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!cmbeid = DLookup("Assigned_to_Eid", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtEID = DLookup("Prepared_by", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtdate = DLookup("Date", "F1NCtbl", Forms!F1NCform!txtscar)
    Any suggestion?

  11. #11
    onlylonely is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    70
    Quote Originally Posted by onlylonely View Post
    Hi All,

    I thought i've solved the issue with Dlookup.

    But i'm not sure why it always show up the data in the table from the 1st row.

    Code:
    Private Sub btnback_Click()
    
    DoCmd.OpenForm ("F1NCForm")
    
    
    Forms!F1NCform!txtscar = Forms!F2CAPAform!txtscar
    
    
    Forms!F1NCform!txtarea = DLookup("Area", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtstatement = DLookup("Statement", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtreq = DLookup("Requirement", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtoe = DLookup("Objective_Evidence", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtlink = DLookup("Hyperlink", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtoe = DLookup("Objective_Evidence", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!cmbdept = DLookup("Assigned_to_dept", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!cmbeid = DLookup("Assigned_to_Eid", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtEID = DLookup("Prepared_by", "F1NCtbl", Forms!F1NCform!txtscar)
    
    
    Forms!F1NCform!txtdate = DLookup("Date", "F1NCtbl", Forms!F1NCform!txtscar)
    Any suggestion?
    Stupid me... i should put a

    "SCAR = Forms!F1NCform!txtscar"

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

Similar Threads

  1. Replies: 7
    Last Post: 10-22-2018, 04:15 PM
  2. Replies: 2
    Last Post: 05-29-2015, 09:21 AM
  3. Replies: 22
    Last Post: 01-23-2014, 02:13 PM
  4. Replies: 1
    Last Post: 03-09-2012, 07:43 PM
  5. Replies: 1
    Last Post: 05-17-2011, 05:19 AM

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