Replies

Hi Charlotte

Perfect, following is completed InputBox code for interest to others:

Last question, InputBox creates a response box the length of the message. Is there a way to force a 1 character response box?

Thanks, John Graves

======================================================
strReturn = InputBox(lngRecords & ” Records matched the criteria you specified.” & _
vbLf & vbLf & vbCr & _
“Select your choice:” & vbLf & vbLf & vbCr & _
” 1 = Full Record View” & vbLf & vbCr & _
” 2 = Data Sheet View” & vbLf & vbCr & _
” 3 = Print Reports”, “Enter your Choice”, ” “)

Select Case strReturn

Case vbNullString
‘strMsg = “Must enter a value, Select an item 1 through 3.”
‘MsgBox strMsg, vbExclamation, Application.Name

Case “1”
DoCmd.OpenForm “frm_TOC_BP_ACD”, WindowMode:=acHidden
Forms!frm_TOC_BP_ACD.RecordSource = “qryResultsBP”
Forms!frm_TOC_BP_ACD.Visible = True

Case “2”
DoCmd.OpenForm “frmqrytblBPermitColSelectedMainForm”, WindowMode:=acHidden
Forms!frmqrytblBPermitColSelectedMainForm!frmqrytblBPermitColSelectedSubForm.Form.RecordSource = “qryResultsBP”
Forms!frmqrytblBPermitColSelectedMainForm.Visible = True

Case “3”
DoCmd.OpenForm “frmSelectReport”, acNormal, acDialog

Case Else
strMsg = “Select an item 1 through 3.”
MsgBox strMsg, vbExclamation, Application.Name

End Select
=============================================

Hi Charlotte

Thanks again for proper coding technique, everything works great.

Case Null, vbNullString (generated RTE 94, Invalid use of null)

Case Null (worked OK)

Any thoughts?

Also

InputBox function provides OK & Cancel buttons.

Cancel returns zero length string and vbNullString seems to pick this up.

Is there any way to detremine if user clicks OK without entering a value?

Thanks, John Graves

This worked for me

‘ following code changes record source for form from tblBPermit to qryResultdBP
DoCmd.OpenForm “frmqrytblBPermitColSelectedMainForm”, WindowMode:=acHidden
Forms!frmqrytblBPermitColSelectedMainForm!frmqrytblBPermitColSelectedSubForm.Form.RecordSource = “qryResultsBP”
Forms!frmqrytblBPermitColSelectedMainForm.Visible = True

This is OK

What happens when users leave and new users start using the system? How many hardcoded objects have to be changed? Could be a maintenance nightmare.

With the following code, its all done once in one place by the system administrator

http://www.DataBaseCreations.com has security tools to take security to the control level.

HTH John Graves

Hi Charlotte

What is difference between Security Manager vrs Security Wizard?

Thanks for info, following includes yours, and other references for loungers that might be interested.

John Graves

http://www.DataBaseCreations.com has security tools to take security to the control level.

HTH John Graves

Hi Charollet

Thanks for your help.

Finally got the following is code to work.

Now that it works with hard coded commands, I would like to execute FormField that has the correct command that I dynamically create based on form and control that pops up the calendar.

How do I eliminate the hard code and execute the correct command that I dynamically connstruct in FormField?

Thank, John Graves

=============
Private Function setdate()
On Error GoTo error_hand

Dim FormField As Variant

‘ copies selected date back to active field
ctldate = Screen.ActiveControl

‘ the following hardcoded code forces a date field After Upade Event trigger based
‘ on form and control that this popup calendar code was double clicked from
If frm.Name = “frmCriteriaBP” And ctldate.Name = “txtEndReceiveDate” Then
Form_frmCriteriaBP.txtEndReceiveDate_AfterUpdate ‘ this hard coded command works
Else
If frm.Name = “frmCriteriaBP” And ctldate.Name = “txtEndIssueDate” Then
Form_frmCriteriaBP.txtEndIssueDate_AfterUpdate ‘ this hard coded command works
Else
If frm.Name = “frmCriteriaBP” And ctldate.Name = “txtEndExpireDate” Then
Form_frmCriteriaBP.txtEndExpireDate_AfterUpdate ‘ this hard coded command works
End If
End If
End If

‘ FormField contains thet correct dynamically created command that i’m hardcoding above,
‘ however I don’t know how to execute the command once constructed in FormField
FormField = “Form_” & frm.Name & “.” & ctldate.Name & “_AfterUpdate”

error_hand:
Resume Next
DoCmd.Close
End Function
==============

I have a form with an unbound start date text box that I double click on to popup up a calendar (date picker).

When I click the calendar a date it copies OK into the start date text box, however the After Update Event text box won

Hi Charlotte

After Update Event Won

Hi Mark

This is my code to popup calendar. Not quite sure where and how to code you

Hi Mark

Thanks for your reply

Find enclosed another response for your interest.

Specifying a filtername only applies anything found in the WHERE clause of
that query to the existing Record Source — it does not use that query as
the new Record Source. If the query is based on tblBPermit, then it should
work. If it’s based on another table that has a similar field structure,
then it won’t work (I’m surprised it doesn’t give you an error).

One way to do it would be to open the form hidden, change its Record Source
property, then reveal it.

DoCmd.OpenForm “frm_TOC_BP_ACD”, WindowMode:=acHidden
Forms!frm_TOC_BP_ACD.RecordSource = “qryResultsBP”
Forms!frm_TOC_BP_ACD.Visible = True

>
> Change Forms Record Source
>
> Using Access 2000 (9.0.4402) SR-1
>
> I have a form name frm_TOC_BP_ACD that has a Record Source of
> tblBPermit that is used for data entry purposes.
>
> For lookup purposes I have a Criteria form which builds a query,
> qryResultsBP based on desired operator criteria.
>
> I have a button on the Criteria form, which performs the following
> commands:
>
> DoCmd.OpenForm “frm_TOC_BP_ACD”, , “qryResultsBP”
>
> frm_TOC_BP_ACD is opening with record source of tblBPermit instead of
> qryResultsBP.
>
> I’m trying to use one form frm_TOC_BP_ACD for two record sources.
>
> How can I get frm_TOC_BP_ACD to open with record source of
> qryResultsBP?
>
> Thanks, John Graves
>
> 1139nb
>

Can’t you use Mark’s suggestion for the log number, then use an autonumber in the same table to link additional records?

Mark

Is it good programming to refer to Company & Application as required with the following:

CompanyCode = left( Command(), 2)

ApplicationType = Right( Command (),2) ‘if exactly 2 bytes

Or, should I store them on database open into global variables?

If variables are best, how do I do that?

Does Command hold startup values throughout the session?

John

Hi Mark

I’m using:

“D:Program FilesMicrosoft OfficeMSO2000OfficeMSACCESS.EXE” “d:access2kbuild.mdb” /cmd “01”

=Command() now contains 01, 02, or 03

Now I want to start the project with two fields Company Code and Application Type /cmd “01 RP”

Then I want to isolate 01 in CompanyCode and RP in ApplType as soon as application starts.

Looking for proper location and syntax to break these two fields into Global Variables, Public Const, ??? so they can be referenced in Forms, Reports and Queries while the application is running

What is the best approach?

Thanks, John Graves

Hi Brian

Thanks for your help, I included another soultion for interested parties.

John Graves

Works OK
addr_no_p: IIf(IsNull(Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & ” “),””, Space(9-Len([strLocStreetNo])) & [strLocStreetNo] & ” “)

Works OK
addr_no_p: Format([strLocStreetNo],”@@@@@@@@@”) & ” “