autopopulate based on two characters in a field

[Mile129]

Can I autopopulate another field in my form based on 2 characters in a another field.



I have a field with a number, i.e. 11275, in the field below it I want to lookup the first two number, "11" and then find the corresponding data it relates to in a table I have, i.e. 11 = MJ.

So in field one someone enters 11275 and then in in the field below it, it automatically enters MJ. Please advise. Thanks.

[HiTechCoach]

To get the left two character from a string use the left function.

Example:

print Left("11275",2)
11

You could then use Dlookup() to get the value.

I owudl use the After Update event to run the code.

[Mile129]

I'm sorry I'm fairly new to Access, can you please be more specific.

I can put the code in as follows:

Private Sub VoyageNo_AfterUpdate()
print Left("what is needed here, Field 1?", 2)
Dlookup("previous result?", "Table name")???
Place where, next field?

End Sub

please advise, thanks.

[HiTechCoach]

Since we no nothing about your database, tbales, fields, etc. ity is very difficult to give you anything specific to your databse.

Note: Fields are in tbales. Controls are on forms/reports. Controls can be bound to a field in the form's/report's record source.

It might be easier to explain if you will to post a sample of your database.

[Mile129]

Okay, sorry I'm not detailed enough, what I want is on my form, when someone enters in a "control" in the form called VoyageNo it updates the table's "field" with the same name. Now, in the control right below VoyageNo is a control called Ship, which currently is a dropdown and they select the ship. What I want is to be able to automatically populate the Ship Control from a table called shipcodes. The first two numbers determine the ship, so in the Shipcodes table it can find ship MJ when you put 11. Is this possible. I have attached the database, it's very simple.

[HiTechCoach]

See if the attach is what you want.

Private Sub VoyageNo_AfterUpdate()

Dim varShipNum As Variant

If Len(Me.VoyageNo) > 2 Then

  varShipNum = DLookup("[Ship]", "tblShipCodes", "[Number]=" & Val(Left(Me.VoyageNo, 2)))

  If IsNull(Me.cboShip) Then
     Me.cboShip = varShipNum
   
  Else
    
    If Me.cboShip <> varShipNum Then
    
      If MsgBox("Update Ship from " & Me.cboShip & " to " & varShipNum & "?", vbYesNo, "Confirm update ") = vbYes Then
         Me.cboShip = varShipNum
      End If

    End If
  End If

End If

End Sub

[Mile129]

Thanks HiTech, seems like the macro is erroring out at:

If IsNull(Me.cboShip) Then
Me.cboShip = varShipNum

I just want the ship control to be autopopulated based on the Voyage Number entry, first two characters of the Voyage number dictate the Ship.

Why can't this be as easy as excel and simple vlookup?!?! j/k, I know access is a whole other beast. please help if you can, thanks.

Oh and when I open the attached database you sent, it's telling me the following: "Unrecognized database format", I'm using Access 2003, if that helps.

[HiTechCoach]

Thanks HiTech, seems like the macro is erroring out at:

If IsNull(Me.cboShip) Then
Me.cboShip = varShipNum

I just want the ship control to be autopopulated based on the Voyage Number entry, first two characters of the Voyage number dictate the Ship.

Why can't this be as easy as excel and simple vlookup?!?! j/k, I know access is a whole other beast. please help if you can, thanks.

Oh and when I open the attached database you sent, it's telling me the following: "Unrecognized database format", I'm using Access 2003, if that helps.

What is the error?

Did you notice that I also renamed the combo box for the Ship to use better naming.


I have reattached the sample I did in a ZIP

[Mile129]

What is the error?

Did you notice that I also renamed the combo box for the Ship to use better naming.


I have reattached the sample I did in a ZIP

That's why, thanks HiTech! it works!

[HiTechCoach]

You're welcome.

Glad that worked for you.