How to tie a field from one db into field from another db??

I have created an Access database that contains three fields that make up a parcel number. The three fields are all alpha-numeric, the first has a field size of 3, the second has a size of 5, and the third has a size of 4. I was wondering if someone would be able to help me in figuring out how to combine the data in these three fields (when the user saves the record) and then search the parcel number field in a different Access database to see if the number being entered matches a parcel number in the other database. So, for example if a user inputs 123 in the first field, 45678 in the second, then 1234 in the third...when they hit the save button, Access combines the fields to 123456781234 and searches the parcel number field in a different database and allows the save if the number exists or throws an error if the number does not exist. Does this make sense? If someone could help with this, I would appreciate it.

Thanks,
Shannon

Shannon,

Concatenating your fields should be fairly easy. On Exit from your last field a simple piece of code would be:

ConcantenatedField = Field1.Value & Field2.Value & Field3.Value

However the look up part might be easiest if you have a linked table in your current database to the other database. Then you can use the DLookup or a FindFirst function in the linked table to see if your ConcantenatedField has a matching record.

DLookup("[ParcelNumber]","LinkedTableName","[ParcelNumber]=ConcantenatedField")

Something simple along those lines is what I would recommend.

lwells

Lwells,

Thank you for your response!! I just need a little more clarification on where I would put the code you suggested. I am new to the programming aspect in Access. Would the concatenating line go in the Save function:


The table in the other database is not the same as the one in the database I created, would this cause problems? The table does have a parcel number field that contains all of the correct parcel numbers. Would I still be able to link to this table, even if it's design is different?

Thanks,
Shannon

Shannon,

Where you put the code would depend on the flow of your application. My thinking would be to check the parcel number against the table after entering the last set of numbers, and then if it didn't match set the focus back to the beginning set of numbers. So I would place the code in the On Exit event of the last text box. Also, the code was generic, so you would actually need to substitute with the actual names of your fields for each one in the code. The ConcantenatedField for example would be replaced with the actual name of the text box that is going to store the concantenated string in. This text box can be either bound to the table or left unbound (recommended) and used only for the purpose of checking the concantenated string against the other table. As far as the other table being a different table, that is not a problem. What I was referring to, was a Linked table. What you would do is open the database window to tables and then right click in an empty area and select Link Tables from the drop down, then follow the prompts to the location and table in the other database. This will add that table to your current database and will stay in sync together. Now you have the table in this database to use.

So the program flow would be, the user enters all three sets of numbers and on the last entry, as soon as the user exits that field, the code would create the concantenated string and place that into the fourth unbound textbox. Then create a command button for example to check to see if it is on the linked table. Place this type of code in the On Click Event for the command button.

Private Sub Command1_Click()
Dim lngConcat As Long

lngConcat = ConcantenatedField

If lngConcat = DLookup("[ParcelNumber]","LinkedTableName","[ParcelNumber]=" & lngConcat) Then
Msgbox "Parcel Number Entered is Valid"
Else
Msgbox "Parcel Number Entered Is Not Valid. Please ReEnter Number"
Field1.SetFocus
End If
End Sub


The above code assumes that the concantenated string of numbers will be all numbers with no letters
Did this help clear up a few things?
lwells

Lwells,

Thanks again for your reply! I created a table to test this with so I could get it correct before linking the actual table. I did get it to work with what you specified, however, I have a few final questions...if you could help me, I'd appreciate it. I was told by my supervisor to go ahead and make the three fields one field for simplification, so this field is txtParcel1. My first question is what do I need to change if the field is a text field...it can be numbers or letters? Second, the code below works if the table I am searching has the same field name as my table. But, what do I need to do if the table's field name is different? For example...my table's field name for parcel number is "Parcel1" and the other table's field name is "KeyNumber". Here's my code that ended up working with the field names in both tables being the same:


Thanks,
Shannon

Hi Shannon,

Because the new control (txtParcel1) will contain both numbers and letters you need to declare the variable as String, not Long.

Dim strParcel As String

The DLookup will use the field name from the other table that holds what you are looking for along with the name of that table. So if the field name for the other table (the one that will eventually be linked) is "KeyNumber" then this portion of the code will look like this:

DLookup("[KeyNumber]","The Name of the Linked Table Here","[KeyNumber]= '" & strParcel & "'")

Because you are now dealing with a string, a couple extra quotes are needed. So your code would look like this:

Private Sub cmdCheckNumber1_Click()
Dim strParcel As String
strParcel = txtParcel1.Value
If strParcel = DLookup("[KeyNumber]", "Name of the Linked Table", "[KeyNumber]='" & strParcel & "'") Then
MsgBox "The Parcel Number You Entered is Valid"
Else
MsgBox "The Parcel Number You Entered Is Not Valid. Please ReEnter Number"
txtParcel1.SetFocus
End If
End Sub

Just put the actual name of the linked table in the place where I typed in bold italics.

That should get you what you need.

lwells

Lwells,

Thanks! It works...now, I just have one more question and then I promise I won't bother you anymore !! I have it working with the command button, however, I would like to get it working in the OnExit event for the text box, so the user can't just skip past the command button. So, I put the code in the OnExit event for txtParcel1 and the pop up messages work correctly, but when I hit tab after entering a number it jumps to the next field whether the number is valid or not. I would like it to automatically go to txtParcel1Acreage if the number is valid, but go back to txtParcel1 if the number is not valid. Do you see anything wrong with my code?



Thanks,
Shannon

Can anyone help me with this, please? I have no idea why this isn't working correctly. Please refer to my post before this one. I need to get the SetFocus property to work when the user enters an invalid keynumber. I would like this to automatically go back to txtParcel1 if the number is invalid so the user cannot continue.

Thanks,
Shannon

Shannon,

Change the code slightly to look like this