2 Questions on Forms - Default Value 0 giving Nulls in some fields; Editing or Adding record

[sara]

Hi -
I have 2 new questions in a new database I'm trying to develop.

The first has 6 fields on a table and allows the user to enter them on
a form. The form is bound to the table.

All 6 fields are default value = 0 on the table and on the form.
Fields are filled in at different times and maybe by different people,
so I wanted them all to "initialize" to 0.

3 of the fields occasionally put Null in the field on the table, so
the report that adds them up shows a blank. I know I can nz the
fields on the report, but I am thinking that since I have default 0 on
the table and the form that might not be necessary and maybe I'm
masking another problem? This is annoying both myself and the user.

The second question is for the same form. An initial form opens where
the user selects an employee, Department and Fiscal Year. I want to
open the second form (the one where the user can enter the 6 fields)
when the user presses the "Go" button. My question is: I want to open
the form to edit a record if it exists, and add a new record if there
isn't one.

Do you do this in code - Dcount and if it's 0, open the form in Add
mode, otherwise open it in Edit mode? Or is there an easier way
(usingA2K).

thanks to all -
sara

[Rich P]

Hi Sara,

Since I can't see your application, it is difficult to see what is going
wrong based on your description here. But I can advise another way -
that my be worth something:

start by building a simple barebones prototype application which does
what the main one will do except in a very limited capacity. For
example, instead of 6 fields in your table and form, start out with 1
field (or maybe 2). Then, don't even worry about the report until you
get the form part fixed. Now you have a simple form that only displays
data from a table - so there is nothing hidden underneath to cause any
problems. And I understand you want to open another form based on some
criteria. The 2nd form is annoying you because it isn't doing what you
want (per what you describer below).

So your prototype will have one form that is based on one table with one
field. When you open the form, it will display the data from that
table. Then you want to open another form (we'll use a command button).

Private Sub Command1_Click()
DoCmd.OpenForm "SecondForm", acNormal
End Sub

Do you need to pass arguments to the 2nd form?

Private Sub Command1_Click()
DoCmd.OpenForm "FormName", , , , , , "Hello|GoodBy|Mary|Lamb"
End Sub

Now you can use the OpenArgs property of the 2nd form

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String

strA = ParseText(OpenArgs, 0)
strB = ParseText(OpenArgs, 1)
strC = ParseText(OpenArgs, 2)
strD = ParseText(OpenArgs, 3)

' Then do what you want with the resulting strings
MsgBox "The OpenArgs are:" & vbNewLine & strA & " " & strB &
vbNewLine & strC & " " & strD

End If
Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, "|", -1)
ParseText = Var(x)

End Function
HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[sara]

On Apr 30, 7:14 pm, Rich P <[e-mail]:

Hi Sara,

Since I can't see your application, it is difficult to see what is going
wrong based on your description here. But I can advise another way -
that my be worth something:

start by building a simple barebones prototype application which does
what the main one will do except in a very limited capacity. For
example, instead of 6 fields in your table and form, start out with 1
field (or maybe 2). Then, don't even worry about the report until you
get the form part fixed. Now you have a simple form that only displays
data from a table - so there is nothing hidden underneath to cause any
problems. And I understand you want to open another form based on some
criteria. The 2nd form is annoying you because it isn't doing what you
want (per what you describer below).

So your prototype will have one form that is based on one table with one
field. When you open the form, it will display the data from that
table. Then you want to open another form (we'll use a command button).

Private Sub Command1_Click()
DoCmd.OpenForm "SecondForm", acNormal
End Sub

Do you need to pass arguments to the 2nd form?

Private Sub Command1_Click()
DoCmd.OpenForm "FormName", , , , , , "Hello|GoodBy|Mary|Lamb"
End Sub

Now you can use the OpenArgs property of the 2nd form

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String

strA = ParseText(OpenArgs, 0)
strB = ParseText(OpenArgs, 1)
strC = ParseText(OpenArgs, 2)
strD = ParseText(OpenArgs, 3)

' Then do what you want with the resulting strings
MsgBox "The OpenArgs are:" & vbNewLine & strA & " " & strB &
vbNewLine & strC & " " & strD

End If

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim Var As Variant
Var = Split(TextIn, "|", -1)
ParseText = Var(x)

End Function

HTH
Rich

*** Sent via Developersdexhttp://www.developersdex.com***

This is a lot for me to digest - need some thinking and research
time. I've never used OpenArgs (at least I don't think I have). Is
that concept well-described in Help? Anywhere else to look?

I keep forgetting that when I have a problem, I should "build" the
app, like you said. Will work on that to see if I can uncover any
issues.
Thanks - for now!
sara

[Rich P]

try copying the code for the 2nd form in the 2nd Form's load event. The
place the mouse cursor over the word "OpenArgs" and press the F1 key.
Help on OpenArgs should come up. It is quite well documented in Access
Help. Note: if you are suggesting that your stage of development with
VBA is still at the early level, the effort you are pursuing here may
require a little more research on VBA usage. Access Help is your
friend. So is the internet (well, for researching this stuff anyway).

Good luck.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[sara]

On Apr 30, 9:24 pm, Rich P <[e-mail]:

try copying the code for the 2nd form in the 2nd Form's load event. The
place the mouse cursor over the word "OpenArgs" and press the F1 key.
Help on OpenArgs should come up. It is quite well documented in Access
Help. Note: if you are suggesting that your stage of development with
VBA is still at the early level, the effort you are pursuing here may
require a little more research on VBA usage. Access Help is your
friend. So is the internet (well, for researching this stuff anyway).

Good luck.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks. Will do. My VBA is still new - maybe Advanced Beginner. I
understand more than I can create from scratch - in other words, I can
copy some code (not all), research the new areas, understand what it's
doing and adapt it for my use - in most cases. I still couldn't use
it from scratch, but I do understand whatever I use.

This will take me a bit, but I'll research OpenArgs.
Thanks again -
Sara

[sara]

On Apr 30, 9:24 pm, Rich P <[e-mail]:

try copying the code for the 2nd form in the 2nd Form's load event. The
place the mouse cursor over the word "OpenArgs" and press the F1 key.
Help on OpenArgs should come up. It is quite well documented in Access
Help. Note: if you are suggesting that your stage of development with
VBA is still at the early level, the effort you are pursuing here may
require a little more research on VBA usage. Access Help is your
friend. So is the internet (well, for researching this stuff anyway).

Good luck.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

So, I'm not getting something. I want to open the second form with
the existing record if there is one and blank to add a record if there
isn't one. I've been reading Help and various posts on OpenArgs and
I'm not seeing how that will happen.

Do I first have to do a Docunt to see if there is a record, then if
there is, use OpenArgs to open the second form right to that record?

And if there isn't a record open the second form in Add Mode?

I just thought Access might somehow handle that all for you- before I
go to the effort of writing the code. (I've been told before that I've
worked "too hard" to write something in code when it's actually built-
in to Access).

thanks
sara