Having a problem with a subform counter. 1

[suprarob]

Hello,

I am a newbie to this forum.
I have a subform that I need to have an incrimental counter. The code that I have been trying to make work, keeps coming back with a runtime error. It won't open the subform.
Form structure
frm_Main
frm_sub1

Thank you for your help,
Rob.

 

[SnaveBelac]

Please post your code, the error and on which line it occurs otherwise we will have no way to sort the problem out.

----------------------------
SnaveBelac - Adventurer

http://www.hostpipe.co.uk

 

[TheAceMan1]

How are ya suprarob . . . . .

[blue]It won't open the subform[/blue]

There's no such thing as opening a subForm!

Be more specific about this!

See Ya! . . . . . .

 

[suprarob]

Sorry about that. Like I said I am a newbie here and on access.
Here is the code:

Dim temp As Integer
Dim msg, Title, Defvalue, Answer As String
'Go to last record and make sure it is not null
DoCmd.GoToRecord A_FORM, "frm_Sub1", A_LAST
If IsNull(Sub_Forms![frm_sub1]![RelayID]) Then
msg = "The value in this field is Null please enter a non Null value"
Title = "RelayId"
Defvalue = 10
Answer = InputBox(msg, Title, Defvalue)
Sub_Forms![frm_sub1]![RelayID] = Answer
Else
temp = Sub_Forms![frm_sub1]![RelayID]
DoCmd.GoToRecord Sub_FORM, "frm_Sub1", A_NEWREC
Sub_Forms![frm_sub1]![RelayID] = temp + 1
End If

The actual error is:
Run-Time error '2489':
The object 'frm_Sub1' isn't open

Any help would be greatly appreciated.

Thank you,
Rob McIntyre

 

[TheAceMan1]

OK suprarob . . . . .

BTW . . . [blue]Welcome to Tek-Tips![/blue]

There's enough wrong with the code, particularly with [blue]DoCmd.GotoRecord[/blue]. [blue]Your not following the prescribed syntax![/blue]

[blue]A subForm is a form embedded within another form . . .[/blue]

Do not call, label, or [blue]refer to a form as a subForm[/blue], unless this is so! You may want to rename [blue]frm_Sub1[/blue] to help keep you out of the habit.

Anyway, your going around the block for what you can accomplish across the street. Try the following ([purple]backup the DB so you can come back to square one if desired[/purple]):

In a module in the modules window, copy paste the following code:

[blue]Function IsOpenForm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

Then replace your code with the following (you substitute any names in [purple]purple[/purple]):

[blue]   Dim frm As Form
   
   If Not IsOpenForm("frm_Sub1") Then
      DoCmd.OpenForm "frm_Sub1", acNormal
   End If
   
   Set frm = Forms!frm_Sub1
   
   DoCmd.GoToRecord acDataForm, "frm_Sub1", acNewRec
   frm!RelayID = DMax("[RelayID]", "[purple][b]TableName[/b][/purple]") + 1
   
   Set frm = Nothing[/blue]

Thats it . . . give it a whirl & let me know . . . .

See Ya! . . . . . .

 

[suprarob]

OK... I'm going to sound dumb, but here it goes.
Do I paste the first code into the form code? And do I need to replace any text with my db info?

Like I said before This is my first shot aat making a db.

So, your patience is appreciated.

Thanks again,
Rob

 

[mustangcoupe]

Rob,

The first bit is a function and needs to be placed in a module..

good luck
Todd

 

[TheAceMan1]

OK suprarob . . . . .

A few pointers first:

[blue]Modules[/blue], the foundation of any Application, let you create libraries of Functions & SubRoutines used throughout your application. Functions always return a value! SubRoutines do not. VBA ([blue]Visual Basic for Applications[/blue]), is the language used to write functions & subroutines in a Module.

There are two types of modules, [blue]Standard & Class[/blue]. [blue]Standard Modules[/blue] are created by going to the modules window and selectind new. Existing [blue]Standard Modules are in this window[/blue]. Class Modules are have a special significance to the programmer, but for now, you just need to know that [purple]Form & Report Modules are Class Modules[/purple]. You will usually find these reffered to as the Form/Report Module, or Code Module. So . . . when someone tells you to put some code in a module, they have to specify Form, Report or Standard Module. I usually specify Standard by saying [blue]a module in the modules window[/blue], since I can't depend on quoting Standard to be understood. Note: all editing in modules is done with the VBE ([blue]Visual Basic Editor[/blue]), which is the editor in control whenever your in a module.

There are several ways to get to these modules so you can edit/copy/paste code. I'll show you how to get there for forms, just bear in mind its the same for Reports:

Open the form in design view. Then on the [blue]Form Design ToolBar[/blue], click the [purple]Code[/purple] button

. This takes you to the [blue]Forms Class Module[/blue]. [purple]This where you view/edit/copy/paste code for this module.[/purple]

There will be may time you will be told to put code in a specific event of a control on the form. To invoke the event in the form module:

Open the form in design view. Call up the properties for the control. Select the [blue]Events Tab[/blue]. Put the cursor on the line for the event, the click the three elipses button

just on the right. This will take you to the Class Module with the specific event in view.

From here you should be able to copy/paste the code where required. Just remember the second code block is replacing what you posted, and [purple]don't forget to backup first![/purple]

Since your new to database, be sure at all costs you understand and know the following links. They represent the heart of designing any database and you really should'nt continue until you understand them well!

[blue]Fundamentals of Relational Database Design[/blue]

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

[blue]Normalizing Tables[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

[blue]Table Relationships[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

[blue]GoodLuck in your Designs![/blue]

See Ya! . . . . . .