Using the same form for adding, changing and viewing data

[Mishanya]

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Brian]

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

 

[Mishanya]

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Brian]

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Mishanya]

Thanks a lot!
(e-mail address removed)

Spasibo! (russian for "thank U!)

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

:

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Mishanya]

just in case, I use Access 2003 - so if Your DB is 2007, it might not work?

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

:

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Brian]

It is on its way via e-mail.

Thanks a lot!
(e-mail address removed)

Spasibo! (russian for "thank U!)

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

:

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

:

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Brian]

It is Access 2003. I have had no reason to upgrade to 2007 as yet.

just in case, I use Access 2003 - so if Your DB is 2007, it might not work?

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

:

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

:

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[Mishanya]

Cool!
Got it - Brian, thanks again a lot!

Mishanya, from somewhere in Russia

It is Access 2003. I have had no reason to upgrade to 2007 as yet.

just in case, I use Access 2003 - so if Your DB is 2007, it might not work?

Mishayana:

It seems complicated at first, but once you have done one form like this,
you will find it is not really so complicated, and you can easily copy your
code & controls from form to form, making adjustments for the specific form.
This is a very necessary function in practically any database. I build
virtually all my forms this way.

I will be glad to send you an example of how I do it if you will post an
e-mail address where I can send it to you.

:

Thank U Brian!
I've been buttering my brains out fo 5 days, trying to accomplish this
presumably simple task. Even tried NotInList tricks - but it only messed the
database up.
Now, would U kindly answer me this:
I've not found in no Access manual or tutorial or Example databases this
kind of form (adding/paging option and combo-searching through the records in
the same form). The solution that U've provided is much more complicated,
then a casual user might think of.
Does it mean that this kind of form-using contradicts any common database
logic and I'd better avoide this?

:

Bind the form to the table.
In the detail section, make a bound control for each field in the table.

In the header, make an UNBOUND combo box (I call it Selector) like this:

RowSource: SELECT from tblClients - include ClientID & ClientName, sorted by
ClientName.
ColumnCount: 2
ColumnWidths: 0",2"
ListWidth: 2"
BoundColumn:1
LimitToList: Yes/true

In the AfterUpdate event of that combo box, us Bookmark to navigate to the
selected record, like this:

Selector_AfterUpdate()

If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ClientID] = " & [Selector]
Me.Bookmark = Me.RecordsetClone.Bookmark
ClientName.SetFocus 'move focus to control on detail section of form
Selector = Null

End Sub

This finds and navigates to the record in your underlying table where the
ClientID matches the ClientID of the selected client.

:

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all its
details and change/update it, but also will have a cboClientName. So that I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing

 

[duane lemery]

I have a simple tblClients with ClientID index, ClientName, CientAdress,
ClientCell etc.
I want to base on it a form wich will be able to add new client and all
its
details and change/update it, but also will have a cboClientName. So that
I
could not only page beetween the records (usual option) but also to pick a
name from the Combo and get fast to the wanted record (Client).
Please advise how can I constract such a thing