Autofill fields

[LG]

Currently we have an excel doc that has the following macro built Sub
Carrier_InfoFill(raddress, xcol)
On Error Resume Next
Static CurrentSheet
Set CurrentSheet = ActiveSheet
'get the carrier from the selected cell...
ncarrier = UCase(CurrentSheet.Range(raddress))
If ncarrier = "" Then Exit Sub
'get the row number of the selected cell...
xrow = CurrentSheet.Range(raddress).Row
'find the sheet that the carrier is located in...
For sn = 2 To 5
nrow = 2

'find the carrier column...
ccol = 1
Do Until Left(Sheets(sn).Cells(1, ccol), 7) = "Carrier"
ccol = ccol + 1
Loop

Do Until Sheets(sn).Cells(nrow, ccol) = ""
'if the carrier is found,
If UCase(Sheets(sn).Cells(nrow, ccol)) = ncarrier Then
With Sheets(sn)
'copy the contents of the 10 columns of the found sheet into the
appeals sheet...
.Range(.Cells(nrow, ccol), .Cells(nrow, ccol + 9)).Copy _
Destination:=CurrentSheet.Cells(xrow, xcol)
End With
End If
'increment the row for the carrier search...
nrow = nrow + 1
Loop

Next sn
End Sub
What this does is when the processor enters a number it automatically fills
the Agency, address and phone numbers in the appropriate fields. I would
like to use this in access in a form? Is this possible? Currently it is
entered into excel and manually transfer them everyday. I know that excel
does a good job with macros for this sort of stuff but, I need the data in
one spot for government regulations and audits.
Any help would be appreciated

 

[Lynn Trapp]

You should be able to simply create a query that provides you with the
carrier information automatically. No need to add that data to another table,
if that is what you are trying to do. Can you describe your Access Database a
little more?

 

[LG]

I have a form built and the processors enter information like name, address,
zip and other personal info that get merged into a letter. For the above it
is a little more complex since the information is for Gov agency's that are
stored and updated quarterly. This is for appeals information and where the
member can go and get paperwork etc in order to file the claim succesfully.
There is already 1 query set up to acknowledge reason codes by number and
print out the actual description. The agency is a little more complicated
since they enter say 1234 and it provides the agency, address, city, state,
zip, phone, and fax. These fields are then merged into a letter and each
field is put in a different part of the letter.
Does this explanation help a little?

 

[Klatuu]

Okay, a query to pull the Agency Information would be what you need:

SELECT [AgencyName], [AgencyAddress], [AgencyCity], [AgencyState],
[AgencyZip] FROM tblAgency WHERE [AgencyCode] = 1234

This is just an aircode example, but that would be how you retrieve the
record for a specific agency.

Without further detail, I can't tell you exactly how to use it.