Results 1 to 5 of 5
  1. #1
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24

    Pass value to text box based on another text box.

    I am currently working on a Job Card form.


    This form pulls data from a 'Jobs' table which stores Account, Customer and Vehicle numbers.

    I have successfully filled some fields with a DLookup function (first name, last name, vehicle make, vehicle model etc.).

    My issue is, when i get to a field such as 'Required Repairs' or 'Vehicle Notes', i need to lookup information that is already saved in the 'Vehicles' table and then edit it if need be.
    Now, i have found that this cannot be done where the Control Field is the 'Dlookup' function.

    I have done a little searching on the net and have found that one guy has achieved what i am after. He says that he has created a text box elsewhere on the form that performs the dlookup, passes that text (on an After_update on said text box) to another text box, which he can then save using VBA.
    Unfortunately he didn't explain much more than this.

    I have limited knowledge on VBA, and have been playing around with codes such a Me.txtVehicleNotes.Value = Me.Text103.Text Or, Me.txtVehicleNotes.Value = Forms!JobCard!Text103.Text.
    All my attempts have followed with a Requery of txtVehicleNotes.

    I should mention that my dlookup box is names Text103 and the box i wish to pass the value to is txtVehicleNotes.

    Any help would be appreciated

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need the .value or .text:
    Me.txtVehicleNotes = Me.Text103
    or
    Me.txtSpeed = Me.txtDist / me.txtTime

    both of these work.
    Also, you dont always need dlookup. you can use combo boxes with 2 columns. 1 col holds the key val, 1 holds the 'dlookup' string val....

    if cboVeh has the columns [VehModel],[vehMake] and shows col 1, Model
    and the picked item shows CAMERO
    the txtMake box can be
    txtMake = cboVeh.column(1) to get the MAKE.
    txtMake will be CHEVY.
    NOTE: in vb code combo boxes start with zero, but in the property window they start with 1.

  3. #3
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Quote Originally Posted by ranman256 View Post
    you dont need the .value or .text:
    Me.txtVehicleNotes = Me.Text103
    or
    Me.txtSpeed = Me.txtDist / me.txtTime

    both of these work.
    Also, you dont always need dlookup. you can use combo boxes with 2 columns. 1 col holds the key val, 1 holds the 'dlookup' string val....

    if cboVeh has the columns [VehModel],[vehMake] and shows col 1, Model
    and the picked item shows CAMERO
    the txtMake box can be
    txtMake = cboVeh.column(1) to get the MAKE.
    txtMake will be CHEVY.
    NOTE: in vb code combo boxes start with zero, but in the property window they start with 1.

    Thanks for the response.

    I'm not using a Combo box to pick the information.
    See the screenshot below, All the customer and vehicle information that is greyed out is successfully using the dlookup method, as is the 'vehicle notes' in the footer (it's only visible at the moment for testing purposes, and will be hidden when it works).
    The Vehicle Notes field in the footer has the dlookup information, which i wish to pass to the empty, 'Vehicle Notes' section in the main body of the form.

    As i said in my original post, a guy on another forum said he used the After_Update on the dlookupbox (Vehicle Notes in the footer a.k.a Text103) to pass the 'looked up' value to the text box (Main body Vehicle Notes a.k.a txtVehicleNotes).

    Everything else works perfectly, i just can't figure out the VBA code to set the value for the main body's Vehicle Notes (txtVehicleNotes).

    This is the current code i've got, but obviously isn't working.

    Code:
    Private Sub Text103_AfterUpdate()   
        Me.txtVehicleNotes = Me.Text103
        Me.txtVehicleNotes.Requery
    End Sub
    Click image for larger version. 

Name:	Capture.jpg 
Views:	11 
Size:	71.4 KB 
ID:	31689

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this DOES work.
    requery is not needed.

    The afterupdate event does not fire until the user presses enter and leaves the field.

  5. #5
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    I got it to work with your help, thanks.

    I added the After_Update event to the 'Booking Date' combo box instead of Text103 and everything works as it should now.

    Thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 03-27-2014, 06:42 AM
  2. Replies: 7
    Last Post: 06-06-2013, 10:32 AM
  3. Replies: 3
    Last Post: 12-22-2012, 05:33 PM
  4. Replies: 15
    Last Post: 04-01-2011, 11:41 AM
  5. Replies: 15
    Last Post: 09-18-2010, 01:19 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
  •  
Other Forums: Microsoft Office Forums