Reading Data from fields on form into VB code

[dcjones]

Hi All,

I have some code that runs a query from a form Command Button. One the form are 2 unbound fields that get data from a another query. How can I use the data in the 2 unbound fields in my code. The 2 unbound fields are [date1] and [date2].

CODE:

DoCmd.OutputTo acOutputQuery, "NDTMS_ReturnQueryTEMP", acFormatXLS, "F:\Ley_Community\Database\NDTMS_Reports\NDTMS.xls", False

Dim xlApp As Excel.Application, xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = False

xlApp.Workbooks.Open "F:\Ley_Community\Database\NDTMS_Reports\NDTMS.xls"
Set xlSheet = xlApp.ActiveSheet

' Below, you need to add the code to do whatever is required in the Excel sheet

'Range("B2").Select
'ActiveWorkbook.save

' Delete the above 2 lines when you have added your code

xlApp.Application.Visible = True

'Clean up here
Set xlSheet = Nothing
Set xlApp = Nothing
End

I have played with this all day with no luck, any help would be good.

Kind Regards,


Dereck.

 

[RichO]

If I'm understanding your question correctly, in VBA you refer to these fields as:

Me.Date1
Me.Date2

Are the NAMEs of these 2 controls Date1 and Date2 or is that the control source? You'll want to refer to the actual name of the control.

 

[dcjones]

Hi Rich, thanks for your reply.

I have a Form which has a query behind it. The query produces records based on a start date and end date. The Form works with no problems.

The 2 unbound fields on the Form show date produced by the query behind the Form, which is the start and end dates. The unbound fields have NAMEs of "date1" and "date2".

On the Form there is a Command Button which runs the Code behind the Button. The Code "DoCmd.OutputTo acOutputQuery, "NDTMS_ReturnQueryTEMP", acFormatXLS, "F:\Ley_Community\Database\NDTMS_Reports\NDTMS.xls", False" calls the same query as the Form, but asks for the start and end dates again.

What I need to do is find some way of passing the start and end dates to the code.

What do you think.

Thanks again Rich.

Kind Regards

Dereck

 

[Surjer]

Does this query "NDTMS_ReturnQueryTEMP" ask for the dates when you run it from the Database Window? or just when calling it from Code?

 

[dcjones]

Hi, Surjer,

Yes it does.

At the start of the process, the user clicks on a Command Button which opens a popup Form where the user inputs the start and end dates. They click on a go Button which in turn Opens the Form based on the "NDTMS_ReturnQueryTEMP" query,

At the bottom of the Form are the 2 unbound fileds "date1! and "date" which show the start and end dates.

Is this of any help.

 

[Surjer]

Can you post what that query looks like?

I am just trying to figure out what method to send the dates using. When it asks for the dates are you displaying custom date pickers or is it just missing query data?

 

[dcjones]

Hi,

I can post the query and here goes:

SELECT Referrals.[Referral Reference], Left([Referrals]![First Name],1) AS FirstIntitial, Left([Referrals]![Last Name],1) AS LastIntitial, Referrals.[Date of Birth], Referrals.Sex, Referrals.[Ethnic Origin], Referrals.[Referral Date], ReferralPCTTable.PCTID, ReferralLATable.LAID, ReferralDATCodeTable.DATID, ReferralGeneralDates.TriageDate, ReferralGeneralDates.AdmissionDate, ReferralGeneralDates.AgencyStartDate, ReferralGeneralDates.Modality, ReferralGeneralDates.ModalityStartDate, ReferralGeneralDates.ModalityEndDate, ReferralGeneralDates.AgencyDischargeDate, ReferralGeneralDates.DischargeReason, ReferralDrugAlcoholUseTable.PrimaruDrug, ReferralDrugAlcoholUseTable.SecondaryDrug1, ReferralDrugAlcoholUseTable.SecondaryDrug2, ReferralDrugAlcoholUseTable.SecondaryDrug3, ReferralDrugAlcoholUseTable.Injected, ReferralDrugAlcoholUseTable.Shared, ReferralGeneralDates.PreviouslyAdmittedDate, LookupDATTable.DAT_Value, LookupLACodeTable.LA_Value, LookupPCTTable.PCT_name
FROM (((ReferralDATCodeTable RIGHT JOIN (ReferralLATable RIGHT JOIN (ReferralPCTTable RIGHT JOIN (ReferralDrugAlcoholUseTable RIGHT JOIN (ReferralGeneralDates RIGHT JOIN Referrals ON ReferralGeneralDates.ReferralID = Referrals.[Referral Reference]) ON ReferralDrugAlcoholUseTable.ReferralID = Referrals.[Referral Reference]) ON ReferralPCTTable.ReferralID = Referrals.[Referral Reference]) ON ReferralLATable.ReferralID = Referrals.[Referral Reference]) ON ReferralDATCodeTable.ReferralID = Referrals.[Referral Reference]) LEFT JOIN LookupPCTTable ON ReferralPCTTable.PCTID = LookupPCTTable.PCTID) LEFT JOIN LookupDATTable ON ReferralDATCodeTable.DATID = LookupDATTable.DATID) LEFT JOIN LookupLACodeTable ON ReferralLATable.LAID = LookupLACodeTable.LAID
WHERE (((ReferralGeneralDates.AdmissionDate) Between [forms]![LookupNDTMSTable]![NTDMSReportdatesFrom] And [forms]![LookupNDTMSTable]![NTDMSReportdatesTo]));

I do hpe you can read it ok.

 

[Surjer]

Ahh Haa - I will have to think a little on this one - the quick and dirty way to do it is to use function sendkeys -

i.e. grab your textbox values like

Dim MyDate1 as Date
Dim MyDate2 as Date

MyDate1 = Date1.Text
MyDate2 = Date1.Text

Then when you get to

"xlApp.Workbooks.Open "F:\Ley_Community\Database\NDTMS_Reports\NDTMS.xls"
Set xlSheet = xlApp.ActiveSheet
"

you can put the sendkeys

SendKeys MyDate1, True

Maybe that will work but should probably think of a better way...

 

[dcjones]

Hi Jerry ,

Thanks for time on this.

I have tried your idea, but is gives an "error 2185 you can't reference a property or method for a control unless the control has the focus.

Here in the UK it's time for bed.

I will start again in the morning. I thank you once again for your time.


Regards


Dereck

 

[Surjer]

Hmm - Will be thinking till I figure this one out!