Link to home
Start Free TrialLog in
Avatar of DanielT
DanielTFlag for Canada

asked on

TextBox Info from another Database

Hi,

I'd like to look up a value in another (Access) database and already have a functional query setup to access the dataset. I created a query based on that query to return only two fields [JOBS_JobName] and [JOBS_JobNo] that is named [qryJOBS_JobNames].

I'd like to use a value in the current database [JobNo] as a filter to return the "JobName" from a match on "JOBS_JobNo = JobNo" and thought I'd be able to use a SQL statement something like that which follows...

="SELECT (JOBS_JobName) FROM (qryJOBS_JobNames) WHERE (JOBS_JobNo) = (me.JobNo);"

Will this work or is it more complicated to have this functional? I just want it to display a name from the "other" database when there's a match. Make sense??
Avatar of DanielT
DanielT
Flag of Canada image

ASKER

Oh - sorry - I wanted the SQL statement to populate a txtBox object's "value".
The query is in the other database?
="SELECT (JOBS_JobName) FROM (qryJOBS_JobNames in 'fullpath\externalDB Name') WHERE (JOBS_JobNo) = (me.JobNo);"
Avatar of DanielT

ASKER

If
DB1 = local, current Access DB
DB2 = remote or "2nd" Access DB

DB1 has a query that uses DB2 as its source so all needed data is available in DB1 through this query. That query was then used as a source to create a simplified query in DB1 and was named "qryJOBS_JobNames", so this query is local.

Does this help?
Well, a little. So what is the issue?

A query can certainly use an external db as source,
generally, as stated, Select * from tablename in 'fullpath\db2Name.mdb'. And that query can be used in DB1 without qualification of DB2 path and name.
Avatar of DanielT

ASKER

Can I use this statement as the controlsource for a text box? I have this entered exactly as shown above but it does not give a result, instead showing the statement. You probably do not need it but see attached. :)
Avatar of DanielT

ASKER

Hmmm, attachment is better when attached...
C-2012-1107-1403-48-.jpg
Is the form bound to a data source? What is source of me.jobno?
Avatar of DanielT

ASKER

The form is bound to a data source but it is not the query I need the "JobName" from. Me.JobNo is a textbox object in a DB1 form bound to the form's data source that would simply display the "JobName" through a textbox using the SQL string.

This database is capturing input for an otherwise independent task but there is additional information available in DB2 (the JobName). I simply want the JobNo field on the DB1 form to match itself to the corresponding entry in DB2 to pull off the JobName.

Do I need to setup a database object in code before I can do this? Or perhaps I cannot bind the txtbox to a SQL command line and need to assign to an interim variable?

The attached shows a portion of the form. The sample JobNo (yellow) would be used to return the JobName (green) using the SQL string. What you see here is what is display on the actual form.
C-2012-1107-1637-44-E1HL.jpg
ASKER CERTIFIED SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DanielT

ASKER

I'll give it a try and let you know.
I have not actually used DLookup before!

Thanks!
Avatar of DanielT

ASKER

Quick Question... when a form is embedded as a sub-form in a main-form, are the sub-forms control names available to the main-form if they are unique without including the sub-form by name in any reference?
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DanielT

ASKER

Jeff,
Great note, I would agree. In this case, there is no editing provided and the display control will be locked - this is just to get some very basic details of the job. But this did make me check the (remote) query as I do not want it to be editable. It was not editable but was a dynaset so I changed the recordset type to be a snapshot.

jerryb30
Thanks for the link!
Avatar of DanielT

ASKER

Hmmm... Just an update.

I did need to change the control reference as shown below as me.JobNo was not adequate (sorry for that rabbit trail).

Now dealing with a Run Time error "2001" that pops up (as attached) when processing the DLookup function. Because of this I decided to try to push the filter parameters through a string in case there was an issue building the parameter inline but it did not help. Am in process of triple-checking names now...

    Dim varLookupResult As Variant
    Dim strFilterParameter As String
   
    strFilterParameter = "JOBS_JobNo ='" & Forms!frmMain!frmMain_JobInfo.Form.JobNo & "'"
    varLookupResult = DLookup("Jobs_JobName", "qryJOBS_JobNames", strFilterParameter)
C-2012-1108-1011-31-.jpg
Avatar of DanielT

ASKER

FYI - Problem likely resolved. Will post again when sure...
Avatar of DanielT

ASKER

Confirmed. Problem is resolved.

There were no object or variable names incorrect but the names used were setup as Captions for consistency within the source query. However, The captions were not recognized by the DLookup function and generated the 2001 run-time error. The following code works in the "On Current" event of the form.

    Dim varLookupResult As Variant
    Dim strFilterParameter As String
   
    strFilterParameter = "RefNo ='" & Forms!frmMain!frmMain_JobInfo.Form.JobNo & "'"
    varLookupResult = DLookup("NameLast", "qryJOBS_JobNames", strFilterParameter)
    Forms!frmMain!frmMain_JobInfo.Form.txtJobName = varLookupResult


THANKS.
Avatar of DanielT

ASKER

Great input. Thanks for helping!