How to set focus to a form and get info from it 1

[citychap26]

Hi

I have the following code from which I want to call a form object and pass the values from the form to the calling module. However the problem is that the code simply pops the form up and then moves to the next bit of code within the module giving me a nice run tinme error 94, how can I stop this?

Sub MakeConnection()

Dim frm As Form_frmConnection
Dim sDSN As String
Dim sPassword As String
Dim sUserName As String
Dim sDatabase As String
Dim sServer As String

'// Open form and let user type in info, then pass back into to this module.
Set frm = New Form_frmConnection

frm.Visible = True


'// Get all the connection details into local variables.

sUserName = frm.txtUserName
sPassword = frm.txtPassword
sDSN = frm.cmbDSN
sServer = frm.txtServer
sDatabase = frm.txtDatabase


MsgBox sUserName & ", " & sPassword

End

Cheers

S

 

[BuilderSpec]

If the form is a standard access form then...


DoCmd.OpenForm FormName, , , , , acDialog

will open a form "FormName" with a dialog type behaviour, control is not passed back to the calling module until the form closes.

 

[citychap26]

Hi,

Thanks for the answer, but I am opening the form as an object trying to keep away from Docmd.

Is there another way??

Cheers

S

 

[mustangcoupe]

just a thought but could you open the form with one set of code then on close gather the information you need?

TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[bamafan2002]

Hello,

I have the following code in a text field called GIS.

=DLookUp("[GIS]","tblFFIncStd","[dteBeginDate] = " & [Forms]![frmFFBudgetEntry]![BeginBudStd]) & " " & DLookUp("[GIS]","tblFFIncStd","[AGSize] = " & [Forms]![frmFFBudgetEntry]![NSAG])

I am trying to pull over data from one table based on two criteria, the date of the standard and the standard based on the AG size. It does fine based on the AG size but the date it will not pull. Can anyone tell me in my code what I have done wrong?

Thanks

 

[PHV]

Replace this:
","[dteBeginDate] = " & [Forms]![frmFFBudgetEntry]![BeginBudStd]) & " " &
By this:
","[dteBeginDate] = #" & Format([Forms]![frmFFBudgetEntry]![BeginBudStd], "mm/dd/yyyy") & "#") & " " &

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bamafan2002]

OK,

When I added the following code:

","[dteBeginDate] = #" & Format([Forms]![frmFFBudgetEntry]![BeginBudStd], "mm/dd/yyyy") & "#") & " " &

then I got stuff I did not need. For example, I should have gotten when the year was 7/1/04 I should have gotten a standard of 1713 and when the year was 7/1/03 I should have gotten 1708. Instead when I entered 7/1/04 I got 3064 1708 and when I changed the yeart to 7/1/03 I got 992 1708. Below is the code as it stands now.

=DLookUp("[GIS]","tblFFIncStd","[dteBeginDate] = #" & Format([Forms]![frmFFBudgetEntry]![BeginBudStd],"mm/dd/yyyy") & "#") & " " & DLookUp("[GIS]","tblFFIncStd","[AGSize] = " & [Forms]![frmFFBudgetEntry]![NSAG])

Thanks!

 

[bamafan2002]

I don't think I explained this well enough to say what I am trying to do. I have 1 table called tblFFIncStd which is my lookup table. I have a second table called tblbudget which my form is based on. I have two fields on the form one called BeginBudStd and the other NSAG. In the lookup table there is fields dteBeginDate and AGSize. When I enter 07/01/2003 in the BeginBudStd and 4 in the NSAG field, I want it to go over to the look table and pull the GIS which should be 1708. If I enter 07/01/2004 it should pull the GIS amount of 1713.

My code currently looks like from the previous reply:

=DLookUp("[GIS]","tblFFIncStd","[dteBeginDate] = #" & Format([Forms]![frmFFBudgetEntry]![BeginBudStd],"mm/dd/yyyy") & "#") & " " & DLookUp("[GIS]","tblFFIncStd","[AGSize] = " & [Forms]![frmFFBudgetEntry]![NSAG])

If I enter a 07/01/2004 date and an 4 in NSAG I get 3064 1708 and when I changed the year to 7/1/2003 I get 992 1708.

I am getting numbers that dont belong plus it is not correctly looking at the year.

Thanks for any help you could provide!

 

[PHV]

and what about this ?
=DLookUp("GIS","tblFFIncStd","dteBeginDate=#" & Format([Forms]![frmFFBudgetEntry]![BeginBudStd],"mm/dd/yyyy") & "# AND AGSize=" & [Forms]![frmFFBudgetEntry]![NSAG])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bamafan2002]

That worked...

Thank you so much... I can adapt it to the other 8 lookups I have to do and I see what is making it work correctly now.

Thanks for all you did!

 

[Zion7]

Hi cityChap26,


Would opening a recordset, be an option?
Maybe use an input box, for the data entry?
Use parameters, in the calling sub?

but, these would defeat the purpose, of using the formConnection.

outside of that CityChap26, i'm at a loss?

Good luck either way!

 

[citychap26]

Hey...

Oh no I wouldn't like to use a input box... that defeats the object of creating a form for input and also would not be very professional.

I think that in an ideal world I'd create the input box in VB and call it.

Cheers
SK