Form recordset question

[Fysh]

I have a form with the record source set to a table. On
the form I have a combo box with the names of people from
this same table. I also have the form data entry set to
no. Now when I open this form it goes to the first
record. When I select a name from the combo box it goes
to the right record select. My question is how do I make
it so when the form opens it goes to a blank form? This
way the user will either have to pick someone in the combo
box to edit their record or they will have to create a new
record. Any suggestions?

thanks for any advice

 

[Dirk Goldgar]

I have a form with the record source set to a table. On
the form I have a combo box with the names of people from
this same table. I also have the form data entry set to
no. Now when I open this form it goes to the first
record. When I select a name from the combo box it goes
to the right record select. My question is how do I make
it so when the form opens it goes to a blank form? This
way the user will either have to pick someone in the combo
box to edit their record or they will have to create a new
record. Any suggestions?

thanks for any advice

You could make an event procedure for the form's Open event, like this:

Private Sub Form_Open(Cancel As Integer)
RunCommand acCmdRecordsGoToNew
End Sub

 

[Fysh]

Thanks, this works fine when the form opens. However, if
I choose a name from the combo box then it gives a runtime
error 3058 message: Index or primary key cannot contain a
Null value. Any other solutions?

 

[Dirk Goldgar]

Thanks, this works fine when the form opens. However, if
I choose a name from the combo box then it gives a runtime
error 3058 message: Index or primary key cannot contain a
Null value. Any other solutions?

This problem can have nothing to do with the code to go to a new record.
There's something you haven't told us about the way the form is set up.
My guess is that your combo box is a bound control -- that is, it has
its ControlSource set to a field in the form's RecordSource -- so when
you select a name from the combo box you are beginning to edit the new
record. Then when your code for the combo box tries to go to the
selected name, Access tries to save that record but can't because some
indexed field hasn't yet been given a value. That's just a guess, but
it seems likely to me.

It's not a good idea for a control that is to be used for navigation,
like your combo box, also to be used for data entry or editing. Thus,
your combo box should be unbound, not bound to a field. Yes, there are
ways to "overload" a control to make this work, ways that involve code
that tries to figure out what you intend whenever you modify the
control's value, but I don't recommend it. If I'm right in my guess
about what's going on, clear out the combo box's ControlSource property
and your problem should disappear.

 

[Fysh]

Actually my combo box is unbound and uses the Row Source
Type Table/Query to give the names to choose from. Then
After Update event I use a bookmark to show the
information on the form. This worked fine before, but it
appears that if the form is at a new record or data entry
mode it can't located the record. Here is my code:

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!cboID.Column(0))
rst.FindFirst "IDNumber = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

 

[Dirk Goldgar]

Actually my combo box is unbound and uses the Row Source
Type Table/Query to give the names to choose from. Then
After Update event I use a bookmark to show the
information on the form. This worked fine before, but it
appears that if the form is at a new record or data entry
mode it can't located the record. Here is my code:

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!cboID.Column(0))
rst.FindFirst "IDNumber = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Hmm, that's odd. The only thing I see wrong with that code is that you
shouldn't be closing the form's RecordsetClone. Delete this line:

rst.Close

However, I don't see why that code would ever give you the error you
describe if the combo box is unbound, unless you have other code, in the
form's Current event maybe, that dirties the record whenever you come to
the new record. Could that be the case? Would you care to post the
complete code from the form's code module?

 

[Fysh1]

The form's module is about 50 pages long if I printed it
out. This started to be a project from someone else and
was given to me to clear up some things. This is one of
those things. I looked at form's Current event and there
is nothing there that edit's the record it just disabled
some combo boxes until a certain procedure is performed.
This record does not get submitted until the end and they
press a submit button. Should I be using a query rather
than the table itself?

 

[Fysh]

I set the navigation buttons to Yes and I am getting the
same error message when I try to go to a different
record. So there must be something else I can use to not
show a record until one has been selected.

 

[Dirk Goldgar]

The form's module is about 50 pages long if I printed it
out. This started to be a project from someone else and
was given to me to clear up some things. This is one of
those things. I looked at form's Current event and there
is nothing there that edit's the record it just disabled
some combo boxes until a certain procedure is performed.
This record does not get submitted until the end and they
press a submit button. Should I be using a query rather
than the table itself?

It's pretty clear to me that *something's* going on behind the scenes
with this form, but it's pretty much impossible for me to say what with
the information you've given me so far. If you'd like to send me a
cut-down copy of your database, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB
in size (preferably much smaller) -- I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message.

 

[Dirk Goldgar]

The form's module is about 50 pages long if I printed it
out. This started to be a project from someone else and
was given to me to clear up some things. This is one of
those things. I looked at form's Current event and there
is nothing there that edit's the record it just disabled
some combo boxes until a certain procedure is performed.

Now that you've sent me the database in question and I've had a look at
it, I can say that you are mistaken. In the Current event procedure you
have a series of statements like this:

Me.RW_D.Enabled = False
Me.RW_D.Value = False
Me.RW_ER.Enabled = False
Me.RW_ER.Value = False
Me.RW_WA.Enabled = False
Me.RW_WA.Value = False
Me.RW_ER_MF.Enabled = False
Me.RW_ER_MF.Value = False
Me.RW_ER_PMS.Enabled = False
Me.RW_ER_PMS.Value = False
Me.RW_ER_AF.Enabled = False
Me.RW_ER_AF.Value = False
Me.RW_DReading.Enabled = False
Me.RW_DReading.Value = False
Me.RW_MCP.Enabled = False
Me.RW_MCP.Value = False
Me.RW_UMCP.Enabled = False
Me.RW_UMCP.Value = False

Yes, half those statements are just disabling these bound check boxes,
but the other half of the statements are setting their values to False.
Doing that in the Current event dirties every record as soon as you come
to it. Therefore, as soon as you try to go to another record, Access
must try to save this "dirty" record. But it can't, because you haven't
filled in the primary key field, so it displays the message you are
seeing.

I don't know what the logic is behind setting all those check boxes to
False each time you come to a record. Do you truly intend to wipe out
whatever values were already stored in the existing record? Without
knowing what your intentions are with this code, I can't tell you how
exactly you should fix the problem. You could simply take out the
statements that sets the values, or you could turn that off only for new
records, or you could do something else entirely that makes sense in the
context of this application.

 

[Fysh]

-----Original Message-----

Now that you've sent me the database in question and I've had a look at
it, I can say that you are mistaken. In the Current event procedure you
have a series of statements like this:

Me.RW_D.Enabled = False
Me.RW_D.Value = False
Me.RW_ER.Enabled = False
Me.RW_ER.Value = False
Me.RW_WA.Enabled = False
Me.RW_WA.Value = False
Me.RW_ER_MF.Enabled = False
Me.RW_ER_MF.Value = False
Me.RW_ER_PMS.Enabled = False
Me.RW_ER_PMS.Value = False
Me.RW_ER_AF.Enabled = False
Me.RW_ER_AF.Value = False
Me.RW_DReading.Enabled = False
Me.RW_DReading.Value = False
Me.RW_MCP.Enabled = False
Me.RW_MCP.Value = False
Me.RW_UMCP.Enabled = False
Me.RW_UMCP.Value = False

Yes, half those statements are just disabling these bound check boxes,
but the other half of the statements are setting their values to False.
Doing that in the Current event dirties every record as soon as you come
to it. Therefore, as soon as you try to go to another record, Access
must try to save this "dirty" record. But it can't, because you haven't
filled in the primary key field, so it displays the message you are
seeing.

I don't know what the logic is behind setting all those check boxes to
False each time you come to a record. Do you truly intend to wipe out
whatever values were already stored in the existing record? Without
knowing what your intentions are with this code, I can't tell you how
exactly you should fix the problem. You could simply take out the
statements that sets the values, or you could turn that off only for new
records, or you could do something else entirely that makes sense in the
context of this application.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks Dirk for taking the time to look at my issue. I
see your point. I will comment these out to see how it
works. It should resolve my problem. Thanks again.

 

[Fysh]

-----Original Message-----


Now that you've sent me the database in question and I've had a look at
it, I can say that you are mistaken. In the Current event procedure you
have a series of statements like this:

Me.RW_D.Enabled = False
Me.RW_D.Value = False
Me.RW_ER.Enabled = False
Me.RW_ER.Value = False
Me.RW_WA.Enabled = False
Me.RW_WA.Value = False
Me.RW_ER_MF.Enabled = False
Me.RW_ER_MF.Value = False
Me.RW_ER_PMS.Enabled = False
Me.RW_ER_PMS.Value = False
Me.RW_ER_AF.Enabled = False
Me.RW_ER_AF.Value = False
Me.RW_DReading.Enabled = False
Me.RW_DReading.Value = False
Me.RW_MCP.Enabled = False
Me.RW_MCP.Value = False
Me.RW_UMCP.Enabled = False
Me.RW_UMCP.Value = False

Yes, half those statements are just disabling these bound check boxes,
but the other half of the statements are setting their values to False.
Doing that in the Current event dirties every record as soon as you come
to it. Therefore, as soon as you try to go to another record, Access
must try to save this "dirty" record. But it can't, because you haven't
filled in the primary key field, so it displays the message you are
seeing.

I don't know what the logic is behind setting all those check boxes to
False each time you come to a record. Do you truly intend to wipe out
whatever values were already stored in the existing record? Without
knowing what your intentions are with this code, I can't tell you how
exactly you should fix the problem. You could simply take out the
statements that sets the values, or you could turn that off only for new
records, or you could do something else entirely that makes sense in the
context of this application.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

F/U Yep that was the problem. I should have seen that.
Taking over someone elses project and trying to decipher
it can be a little difficult and becomes blurry. Thanks
for your help.

 

[Guest]

Hi,

I would like to do something similar to this, but instead of using the
recordset, can I use a query to populate the comboBox? the query links 2
tables with a left-join, to pull-up information based on the comboBox, which
is the ID#

The problem is that, when I remove the Countrol Source (EmployeeID) from the
ComboBox, the remaining fields do not automatically populate.

The reason I can't leave EmployeeID as the control source, is that as I
navigate through the records, my data will become corrupted with duplicated
EmployeeID's.

I'm not set on any one method, I just want to use a comboBox to display a
list of existing EmployeeID's and then have the remaining fields populated
with the corresponding data from the tables.

(example: I select cooltest.employeeID #4, then employee #4's name, age,
phone#, etc are pulled from table:Employees automatically)

sql:
SELECT DISTINCTROW cooltest.EmployeeID, Employees.LastName,
Employees.FirstName, Employees.Title, Employees.Address, Employees.City,
Employees.PostalCode, Employees.PostalCode, Employees.PostalCode,
Employees.Country, Employees.HomePhone, cooltest.dateToDetroit,
cooltest.dateFromDetroit
FROM cooltest LEFT JOIN Employees ON cooltest.EmployeeID =
Employees.EmployeeID;

 

[Dirk Goldgar]

Hi,

I would like to do something similar to this, but instead of using the
recordset, can I use a query to populate the comboBox? the query
links 2 tables with a left-join, to pull-up information based on the
comboBox, which is the ID#

The problem is that, when I remove the Countrol Source (EmployeeID)
from the ComboBox, the remaining fields do not automatically populate.

The reason I can't leave EmployeeID as the control source, is that as
I navigate through the records, my data will become corrupted with
duplicated EmployeeID's.

I'm not set on any one method, I just want to use a comboBox to
display a list of existing EmployeeID's and then have the remaining
fields populated with the corresponding data from the tables.

(example: I select cooltest.employeeID #4, then employee #4's name,
age, phone#, etc are pulled from table:Employees automatically)

sql:
SELECT DISTINCTROW cooltest.EmployeeID, Employees.LastName,
Employees.FirstName, Employees.Title, Employees.Address,
Employees.City, Employees.PostalCode, Employees.PostalCode,
Employees.PostalCode, Employees.Country, Employees.HomePhone,
cooltest.dateToDetroit, cooltest.dateFromDetroit
FROM cooltest LEFT JOIN Employees ON cooltest.EmployeeID =
Employees.EmployeeID;

I'm not sure I follow you. Of course you can use a query as the
rowsource of your combo box, and use that combo box to find the matching
record on your form. For such uses, as I said earlier, it's best if the
combo box is unbound, so that you don't have to take extraordinary
measures to keep from corrupting your data.

So what exactly are you asking? What is not working for you?

 

[Guest]

Hi,

Sorry for that vague message. here's what I want and there is also some sql
listed below.

I want to use 2 tables,
table_a(addr) should have an employeeID and some other information that I
would like to update.

table_a(addr) is also used in the comboBox to, to select the employeeID for
editing and.

Table_b(employeeID) should just have some static data. that is displayed for
reference.

PROBLEM:
when I follow your suggestion, and us an unbound comboBOX, the data being
retreived from table_b(employeeID) stops displaying on the screen. some link
is broken, if i put the employeeID back in as a bound column, then the
employeeID pulls the matching data from table_b(employeeID)

combobox rowsource:
SELECT DISTINCT addr.employeeid FROM addr;

SQL query from the forms query:
(maybe I don't need this ??? )
SELECT addr.employeeID, Employees.LastName, Employees.FirstName,
Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
Employees.HireDate, Employees.Address, Employees.City, Employees.Region,
Employees.PostalCode, addr.age, addr.phone
FROM addr INNER JOIN Employees ON addr.employeeID = Employees.EmployeeID;

RELATIONSHIP:
addr is linked to employeeID,
on employeeID
include all fields from addr and only those from employeeID where the
fields are equal



I hope I've given you all the information. If i left something out, let me
know.


thanks,

phill

 

[Dirk Goldgar]

Hi,

Sorry for that vague message. here's what I want and there is also
some sql listed below.

I want to use 2 tables,
table_a(addr) should have an employeeID and some other information
that I would like to update.

table_a(addr) is also used in the comboBox to, to select the
employeeID for editing and.

Table_b(employeeID) should just have some static data. that is
displayed for reference.

PROBLEM:
when I follow your suggestion, and us an unbound comboBOX, the data
being retreived from table_b(employeeID) stops displaying on the
screen. some link is broken, if i put the employeeID back in as a
bound column, then the employeeID pulls the matching data from
table_b(employeeID)

combobox rowsource:
SELECT DISTINCT addr.employeeid FROM addr;

SQL query from the forms query:
(maybe I don't need this ??? )
SELECT addr.employeeID, Employees.LastName, Employees.FirstName,
Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
Employees.HireDate, Employees.Address, Employees.City,
Employees.Region, Employees.PostalCode, addr.age, addr.phone
FROM addr INNER JOIN Employees ON addr.employeeID =
Employees.EmployeeID;

RELATIONSHIP:
addr is linked to employeeID,
on employeeID
include all fields from addr and only those from employeeID where
the fields are equal



I hope I've given you all the information. If i left something out,
let me know.


thanks,

phill

I'm terribly sorry, it seems I overlooked this reply from you. Are you
still looking for help on this subject, or have you managed to solve it
already?

 

[Guest]

HI,

Thanks for thinking of me again.

Yes, I still need some help with this problem.


thanks,

phill

 

[Dirk Goldgar]

Hi,

Sorry for that vague message. here's what I want and there is also
some sql listed below.

I want to use 2 tables,
table_a(addr) should have an employeeID and some other information
that I would like to update.

table_a(addr) is also used in the comboBox to, to select the
employeeID for editing and.

Table_b(employeeID) should just have some static data. that is
displayed for reference.

PROBLEM:
when I follow your suggestion, and us an unbound comboBOX, the data
being retreived from table_b(employeeID) stops displaying on the
screen. some link is broken, if i put the employeeID back in as a
bound column, then the employeeID pulls the matching data from
table_b(employeeID)

combobox rowsource:
SELECT DISTINCT addr.employeeid FROM addr;

SQL query from the forms query:
(maybe I don't need this ??? )
SELECT addr.employeeID, Employees.LastName, Employees.FirstName,
Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
Employees.HireDate, Employees.Address, Employees.City,
Employees.Region, Employees.PostalCode, addr.age, addr.phone
FROM addr INNER JOIN Employees ON addr.employeeID =
Employees.EmployeeID;

RELATIONSHIP:
addr is linked to employeeID,
on employeeID
include all fields from addr and only those from employeeID where
the fields are equal

I hope I've given you all the information. If i left something out,
let me know.

Okay, I've been reading over this, and I think I understand what you're
trying to do. Let me recap. You have a form based on a query that
joins the tables [addr] and [Employees] on EmployeeID. You have a combo
box bound to the addr.EmployeeID field. You are also using this combo
box to navigate from employee to employee on your form, and therein lies
the problem: if you select a different EmployeeID in the combo box, you
naturally change the EmployeeID of the current record. So this combo
box must be unbound.

But there's something wrong here, because you also say:

when I follow your suggestion, and us an unbound comboBOX, the data
being retreived from table_b(employeeID) stops displaying on the
screen. some link is broken, if i put the employeeID back in as a
bound column, then the employeeID pulls the matching data from
table_b(employeeID)

If you mean that none of the fields that the recordsource query selects
from the Employees table (LastName, FirstName, Title, TitleOfCourtesy,
BirthDate, HireDate, Address, City, Region, PostalCode) will now display
on the form, then something is not set up the way I expected. That's
not what should happen, and not what happens in my simple test.

Are you displaying these fields in a subform on the main form? If
that's the case, that would be why it's breaking -- but your query makes
that unnecessary, as it joins the tables. If that's not it, you're
going to have to tell me more details about the form and its controls,
and about the tables, their fields, and their indexes.

 

[Guest]

maybe this will help me.

looking at the northwinds sample database. the orders form.

the orders form does almost exactly what I want to do. They use customerID
as the main field, and they have customerID as the controlSource.

When I try to do the same thing using different tables. My ControlSource
causes the field, when changed, to change the addr:employeeID
eventually, all the addr:employeeID's are the same.

Here's more detail on what I have done:

I'm trying to do almost the same thing as the northwinds database, on the
orders form.

table:addr
employeeID
item1
item2
item3 - (example salary)

table:employees (northwinds employee table)


draw relation from addr:employeeID to employees:employeeID

create query1 from all fields in table:addr, and
table:employees, names, age, etc
SQL view query1:
SELECT addr.employeeID, addr.item1, addr.item2, addr.item3,
Employees.LastName, Employees.FirstName, Employees.Title, Employees.BirthDate
FROM Employees INNER JOIN addr ON Employees.EmployeeID = addr.employeeID;


use form wizard to create a form from query1
change employeeid field to ComboBox (cboEmployeeID)

employeeID combobox: row source
SELECT addr.employeeid FROM addr;

if i leave the cboEmployeeID control source as employeeID, things seem to
work. but when i check the ADDR table, i find all the addr:employeeID's have
changed to 2 or sometimes 3.

if i change field:employeeID and remove the control source. then the
employee lastname, firstname, title, birthdate, etc do not automatically
change. as I change the cboEmployeeID


I think I've walked through the whole process.

 

[Dirk Goldgar]

maybe this will help me.

looking at the northwinds sample database. the orders form.

the orders form does almost exactly what I want to do. They use
customerID as the main field, and they have customerID as the
controlSource.

When I try to do the same thing using different tables. My
ControlSource causes the field, when changed, to change the
addr:employeeID eventually, all the addr:employeeID's are the same.

Here's more detail on what I have done:

I'm trying to do almost the same thing as the northwinds database, on
the orders form.

table:addr
employeeID
item1
item2
item3 - (example salary)

table:employees (northwinds employee table)


draw relation from addr:employeeID to employees:employeeID

create query1 from all fields in table:addr, and
table:employees, names, age, etc
SQL view query1:
SELECT addr.employeeID, addr.item1, addr.item2, addr.item3,
Employees.LastName, Employees.FirstName, Employees.Title,
Employees.BirthDate FROM Employees INNER JOIN addr ON
Employees.EmployeeID = addr.employeeID;


use form wizard to create a form from query1
change employeeid field to ComboBox (cboEmployeeID)

employeeID combobox: row source
SELECT addr.employeeid FROM addr;

if i leave the cboEmployeeID control source as employeeID, things
seem to work. but when i check the ADDR table, i find all the
addr:employeeID's have changed to 2 or sometimes 3.

Of course. I already explained why that happens.

if i change field:employeeID and remove the control source. then the
employee lastname, firstname, title, birthdate, etc do not
automatically change. as I change the cboEmployeeID

This is the part that seems odd to me. Oh, wait a minute! I think I
get it. You don't actually have any code to use that combo box for
navigation, do you? All you're doing by changing the combo box is

(a) if it's bound, changing the EmployeeID in the current addr
record (so the Employee fields change to match, but you are corrupting
your data), or

(b) if it's unbound, doing nothing but changing what the combo box
displays.

I think I've walked through the whole process.

Maybe I'm mistaken, but I don't think the Northwind Orders form is
actually doing what you have in mind. The Customers combo box there is
actually changing the CustomerID in the order record, because that form
is for entering an order for a customer. I had the impression that what
you want to do with your combo box is move to the matching record on
your form, *not* change the EmployeeID in the current record. Am I
right?

If I am right, then your combo box should definitely be unbound.
However, you need to add some code to make it navigate the form. With
the form open in Design View, click the "Code" button on the toolbar, or
menu items View -> Code, to open the form's code module. Then paste in
this code:

'----- start of code -----
Private Sub EmployeeID_AfterUpdate()

If Not IsNull(Me.EmployeeID) Then
Me.Recordset.FindFirst "EmployeeID=" & Me.EmployeeID
End If

End Sub
'----- end of code -----

Just in case I have made a mistake, click Debug -> Compile to compile
your project. If any errors turn up, correct them if you can, report
them to me if you can't. Then save the form, switch it to Form View,
and see if it now does what you want.