problem with editing new record in form

[Dos Equis]

Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the form
and then enter a new name. I would rather have command button at the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make it
happen?

Thank you,

Byron

 

[Allen Browne]

Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store surnames
and company names, and OtherName to store first names and trading names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to another
record.

 

[Dos Equis]

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store surnames
and company names, and OtherName to store first names and trading names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to another
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the form
and then enter a new name. I would rather have command button at the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make it
happen?

Thank you,

Byron

 

[Allen Browne]

There's lots of ways to handle this, but how does this sound:
- a combo where you choose the field to search (first/last/company)
- a text box where you enter the value to match.
See:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That example is incredibly simple to set up. You copy the module to your
database, combo and text box to your form, and set one property.

It would be possible to create another interface that dynamically alters the
RowSource of your combo so it always retains ClientID as the (hidden) bound
field, but shows the other column(s) in any way you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store surnames
and company names, and OtherName to store first names and trading names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to another
record.

Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the form
and then enter a new name. I would rather have command button at the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make it
happen?

Thank you,

Byron

 

[Dos Equis]

Allen,

I am trying to use your COmbo box code to search records in my
database. I've finally got the code in my modules section but when I
run it on the form, I get this error

Compile Error:

User-defined type not defined.

I've loked in my books and online, but haven't found a soloution.
Would you happen to know what this is talking about and what I need to
look at to fix it?
Thank you,

Byron

There's lots of ways to handle this, but how does this sound:
- a combo where you choose the field to search (first/last/company)
- a text box where you enter the value to match.
See:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That example is incredibly simple to set up. You copy the module to your
database, combo and text box to your form, and set one property.

It would be possible to create another interface that dynamically alters the
RowSource of your combo so it always retains ClientID as the (hidden) bound
field, but shows the other column(s) in any way you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store surnames
and company names, and OtherName to store first names and trading names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to another
record.


Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the form
and then enter a new name. I would rather have command button at the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make it
happen?

Thank you,

Byron

 

[Dos Equis]

p.s. There are 2 lines highlighted, this one in yellow:
Public Function FindAsUTypeLoad(frm As Form, ParamArray
avarExceptionList()) As Boolean

and this one in blue (Everything in the parenthasese):

Dim (rs As DAO.Recordset)

Thanks,

Byron

There's lots of ways to handle this, but how does this sound:
- a combo where you choose the field to search (first/last/company)
- a text box where you enter the value to match.
See:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That example is incredibly simple to set up. You copy the module to your
database, combo and text box to your form, and set one property.

It would be possible to create another interface that dynamically alters the
RowSource of your combo so it always retains ClientID as the (hidden) bound
field, but shows the other column(s) in any way you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store surnames
and company names, and OtherName to store first names and trading names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to another
record.


Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the form
and then enter a new name. I would rather have command button at the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make it
happen?

Thank you,

Byron

 

[Allen Browne]

You need to add a reference to the Microsoft DAO 3.6 Library.

From the code window, choose References on the Tools menu.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

p.s. There are 2 lines highlighted, this one in yellow:
Public Function FindAsUTypeLoad(frm As Form, ParamArray
avarExceptionList()) As Boolean

and this one in blue (Everything in the parenthasese):

Dim (rs As DAO.Recordset)

Thanks,

Byron

There's lots of ways to handle this, but how does this sound:
- a combo where you choose the field to search (first/last/company)
- a text box where you enter the value to match.
See:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That example is incredibly simple to set up. You copy the module to your
database, combo and text box to your form, and set one property.

It would be possible to create another interface that dynamically alters
the
RowSource of your combo so it always retains ClientID as the (hidden)
bound
field, but shows the other column(s) in any way you wish.

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Allen Browne wrote:
Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store
surnames
and company names, and OtherName to store first names and trading
names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS
SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the
desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to
another
record.


Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than
have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the
form
and then enter a new name. I would rather have command button at
the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make
it
happen?

 

[Dos Equis]

That worked. Thank you.

Byron

You need to add a reference to the Microsoft DAO 3.6 Library.

From the code window, choose References on the Tools menu.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

p.s. There are 2 lines highlighted, this one in yellow:
Public Function FindAsUTypeLoad(frm As Form, ParamArray
avarExceptionList()) As Boolean

and this one in blue (Everything in the parenthasese):

Dim (rs As DAO.Recordset)

Thanks,

Byron

There's lots of ways to handle this, but how does this sound:
- a combo where you choose the field to search (first/last/company)
- a text box where you enter the value to match.
See:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
That example is incredibly simple to set up. You copy the module to your
database, combo and text box to your form, and set one property.

It would be possible to create another interface that dynamically alters
the
RowSource of your combo so it always retains ClientID as the (hidden)
bound
field, but shows the other column(s) in any way you wish.

Allen,

This sounds like the answer I may be looking for, the problem is that I
must have three name fields, First, Last and Company as I send some
papers to a specific person at company name. I don't mind looking
through more than one field just need to know how to have it search
them automatically instead of needing to combine all of them into 1.
Or, does this search look for partial matches rather than whole field?

Allen Browne wrote:
Sounds like your table contains both persons and companies.
The search will depend on how your fields are set up.
My preferred approach is to use a field called MainName to store
surnames
and company names, and OtherName to store first names and trading
names.
Presumably you also have a primary key field such as ClientID.

If that's the setup, your combo will have properties like this:
Row Source SELECT ClientID,
MainName & ", " + [OtherName] AS
SearchName
FROM Clients
ORDER BY MainName, OtherName;
Column Count 2
Column Widths 0
Control Source {leave blank}

You can then use the AfterUpdate event of this combo to find the
desired
record in the form. The code will look like this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

With that approach, there is no need to close the form to move to
another
record.


Hi all,

I have a form based on a query.

The query looks up records once you input the last name or company
name.
(I would like to be able to put either in the same box rather than
have
to enter past the first box to the second)

Once the form opens the record, I can edit data for any field.

My problem is once I've finished with the first record, if I want to
edit another one, I have to close the record, double click in the
form
and then enter a new name. I would rather have command button at
the
pottom of the form that allows me to edit a second record.

So, Is this possible, and if so, what should I be looking at to make
it
happen?