Navigate related records

[Bob]

Hello:

I'm building a simple windows database application that has two tables with
One to One relationships on MS Access tables.

I know what your thinking. Why not just use one table? because MS Access
has a field count limitation of 255 and my table would have 327 fields so I
have no choice.

I'm using two Data adapters that generate a single DS with a relationship
build on the ClientID fields.

I dont' wish to use a datagride because the customer doesn't want it.

I can't figure out how to make my app populate the fields in my form with
the child data when the dataset loads and moves to the next record.

Below is what I have and it works ok:

Me.BindingConte xt(objdsClients , "ClientsMain"). Position =
(Me.BindingCont ext(objdsClient s, "ClientsMain"). Position +
1)Me.BindingCon text(objdsClien ts, "ClientsPage01" ).Position =
(Me.BindingCont ext(objdsClient s, "ClientsPage01" ).Position + 1)
But as you know, it can quickly cause problems later if the child table
doesn't have a child record added for some reason like a power failur or
something else.

I built the relationship in Query Designer

Any help would be appriciated

TIA

Bob

[Cor Ligthert]

Re: Navigate related records

Bob,

Why do you make it yourself difficult, why don't you use just two datatables
in one dataset. They only need to have the same key.

It would have been a problem if you was using a datagrid, however you don't
have that problem.

By the way, I am curious how do you get 327 textboxes on one form?

I hope this helps,

Cor

[Terry Burns]

Re: Navigate related records

LOL

--
Terry Burns

http://TrainingOn.net

"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:%239Dh5$1b FHA.1152@tk2msf tngp13.phx.gbl. ..[color=blue]
> Bob,
>
> Why do you make it yourself difficult, why don't you use just two
> datatables in one dataset. They only need to have the same key.
>
> It would have been a problem if you was using a datagrid, however you
> don't have that problem.
>
> By the way, I am curious how do you get 327 textboxes on one form?
>
> I hope this helps,
>
> Cor
>[/color]

[Bob]

Re: Navigate related records

Cor:

Yeah thats why I don't understand why it doesn't work. I am using just one
dataset.

Maybe I only need one oleDBDataAdapte r also and join the tables there?
Anyway, I tried that but all I got was constraint probs.

The examples I see use two oleDBDataAdapte rs and generate one DS.

I found an example that uses a Combo Box and I'm not. I might be making
headway with the code. Not sure yet.


Thanks

Bob

[Cor Ligthert]

Re: Navigate related records

Bob,

Why do you not, show us some snippets of your code, (Not the designer part
pleast that we know very much). And than first copied, pasted, copied pasted
with a notebook to get it readable.

Cor

[Bob]

Re: Navigate related records

Private Sub btnNavNext_Clic k(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnNavNext.Clic k
Me.BindingConte xt(objdsClients , "ClientsMain"). Position =
(Me.BindingCont ext(objdsClient s, "ClientsMain"). Position + 1)
'Me.BindingCont ext(objdsClient s, "ClientsPage01" ).Position =
(Me.BindingCont ext(objdsClient s, "ClientsPage01" ).Position + 1)
'Me.txtSBLNum.D ataBindings.Add (New
System.Windows. Forms.Binding(" Text", objdsClients,
"ClientsMain.PK _ClientID.Clien tID"))


Dim SelectClientID As String
SelectClientID = editClientID.Te xt.ToString()
Dim drSelectedClien t As DataRow
drSelectedClien t =
objdsClients.Cl ientsMain.FindB yClientID(Selec tClientID)
' Declare an array of data rows to hold the related records.
Dim draPage01 As DataRow()
draPage01 = drSelectedClien t.GetChildRows( "ClientsPage01" )
' Display the length of the array (number of orders)
' and the CustomerID in the form caption.
'Me.Text = draPage01.Lengt h.ToString() & " Client Pages " &
SelectClientID

Dim drPage1 As DataRow
For Each drPage1 In draPage01
'lbOrders.Items .Add(drOrder("O rderID"))
Next

Me.objdsClients _PositionChange d()

End Sub

[Bob]

Re: Navigate related records

Cor:

Yeah I'm curious how I'm going to get 327 fields on a form too. I don't
have anything briliant but I think I'm going to put as many as I can into
groupboxes and use the visible property in my code to show and hide the
fields consecutively as the user enters data.

Bob

[Cor Ligthert]

Re: Navigate related records

Bob,

Can you try this sample that I made for you, it uses only two textboxes and
a button, however the problem with the two datatables and the solution
should be the same.

\\\Needs only two textboxes and a button on a form and pasting this code in
Dim ds As DataSet
Private Sub Form1_Load(ByVa l sender As System.Object, _
ByVal e As System.EventArg s) Handles MyBase.Load
ds = CreateDs()
Me.TextBox1.Dat aBindings.Add(" Text", ds.Tables(0), "Country")
Me.TextBox2.Dat aBindings.Add(" Text", ds.Tables(1), "State")
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, _
ByVal e As System.EventArg s) Handles Button1.Click
Dim cma1 As CurrencyManager _
= DirectCast(Bind ingContext(ds.T ables(0)), CurrencyManager )
Dim cma2 As CurrencyManager _
= DirectCast(Bind ingContext(ds.T ables(1)), CurrencyManager )
If cma1.Position < ds.Tables(0).Ro ws.Count - 1 Then
cma1.Position += 1
Else
cma1.Position = 0
End If
cma2.Position = cma1.Position
End Sub
Private Function CreateDs() As DataSet
Dim ds As New DataSet
Dim dtName As New DataTable("Pers ons")
Dim dtCountry As New DataTable("Coun tries")
dtCountry.Colum ns.Add("Country ")
ds.Tables.Add(d tCountry)
dtCountry.LoadD ataRow(New Object() {"EU"}, True)
dtCountry.LoadD ataRow(New Object() {"EU"}, True)
dtCountry.LoadD ataRow(New Object() {"US"}, True)
dtCountry.LoadD ataRow(New Object() {"US"}, True)
Dim dtStates As New DataTable("Stat es")
dtStates.Column s.Add("State")
ds.Tables.Add(d tStates)
dtStates.LoadDa taRow(New Object() {"Austria"}, True)
dtStates.LoadDa taRow(New Object() {"Holland"}, True)
dtStates.LoadDa taRow(New Object() {"Florida"}, True)
dtStates.LoadDa taRow(New Object() {"New York"}, True)
Return ds
End Function
///

I hope this helps,

Cor

[Bob]

Re: Navigate related records

I don't think it will work because ds doesn't get declared in the button
click and the form load events but I'll try it around 9:30 am EDT

[Bob]

Re: Navigate related records

ooppss my bad. I see it

[Bob]

Re: Navigate related records

Cor:

ok, I tried it and got the same runtime error

An unhandled exception of type 'System.NullRef erenceException ' occurred in
WindowsApplicat ion1.exe

Additional information: Object reference not set to an instance of an
object.

Same as I get in my project

Thanks again

Bob

[Bob]

Re: Navigate related records

sorry, the code highlighted is

ds.Tables.Add(d tCountry)



Bob

[Cor Ligthert]

Re: Navigate related records

Bob,

I tried this sample when I send it, and now again, I had/have not any error.

Can you paste it back to show what you made from it?

Cor

[Bob]

Re: Navigate related records

Cor:

Once again, I appologize for the confusion.

these early mornings without that first cup of coffe.

Me in my infinite wisdome, I forgot to past it into notepad first.

Thanks for all the help and sorry for all the false alarms.

I'll try to implement this into my project

Thanks again

Bob

[Bob]

Re: Navigate related records

Cor:

I appriciate the help you have given me but now that I look at it, I already
had a form of that. Thats based on row position.

That won't help me because thats not 100%. A simple power failure can throw
that off too easily. If data gets entered into the parent table and for some
reason the users stop entering data into the child tables, power failure,
system hang etc ...

See where I'm going?

I need something based on real relational concepts. I spent money on VS
becaused it advertised that but I'm thinking I'm better off with what I know
(VS6).

Any other suggestions?

Thanks again

Bob