Text Box Populated With Wrong Column

[newt]

Driving me nuts, but probably something simple. I have a form where a text
box should populate with column 11 of a separate list box, which is a date
field:

Me.txtDateStamp.Value = Me.lstTask.Column(11)

 

[newt]

Sorry, the forum is buggy.... the problem is it populates with column 12
instead of 11! Maybe this has to do with trying to populate a text box with
a date field? Thanks in advance.

 

[John W. Vinson]

Sorry, the forum is buggy.... the problem is it populates with column 12
instead of 11! Maybe this has to do with trying to populate a text box with
a date field? Thanks in advance.

The Column() property is zero based: the first column is Column(0), the
twelfth is Column(11).

Use Column(10).

 

[newt]

Thanks, I know that, but it's still calling the column to the right of the
one I need. If I reduce the number by one, it calls the column to the
left....

 

[Piet Linden]

Sorry, the forum is buggy....  the problem is it populates with column 12

you sure you don't have a column in there with a width of 0"?

 

[John W. Vinson]

Thanks, I know that, but it's still calling the column to the right of the
one I need. If I reduce the number by one, it calls the column to the
left....

Well, you didn't SAY that.

Check the ColumnCount property of the combo - is it 12 or more?
Perhaps you could also post the rowsource SQL.

If you look at the rowsource query datasheet do you see the expected values in
the 11th field?

 

[Piet Linden]

Well, you didn't SAY that.

Check the ColumnCount property of the combo - is it 12 or more?
Perhaps you could also post the rowsource SQL.

If you look at the rowsource query datasheet do you see the expected values in
the 11th field?

Awww, c'mon John!!! He was testing your clairvoyance skills!

 

[newt]

Thanks John. Indeed, my RowSource query did not contain the column. The
RowSource query is generated when another field is populated, but rather than
looking at the RowSource query to find the problem, I was looking at a
QueryBuilder somewhere that did have the column -- and now, for the life of
me, I can't find the QueryBuilder that I was looking at before. Oh well.

Now I have another problem. I keep getting a syntax error that I know has
something to do with a Null value problem. My code is:

ssql = "insert into x_task (project_id, task_subcategory_id,
task_description, supervisor_id, lead_id, " & _
"junior_id, staffing_notes, task_status_id, hot_potato_id,
task_status_notes) " & _
"values (" & Me.lstProject.Value & "," & Me.lstTaskSubType.Value &
",'" & _
dbl_quotes(Me.txtTaskNotes) & "'," & Me.lstSupervisor.Value & "," &
Me.lstLead.Value & "," & intJunior & ",'" & _
dbl_quotes(Me.txtStaffNotes) & "'," & Me.lstTaskStatus.Value & "," &
intHotPotato & ",'" & _
dbl_quotes(Me.txtStatusNotes) & "');"

where I have intJunior and intHotPotato defined as follows:

If IsNull(Me.lstHotPotato.Value) Then
intHotPotato = Null
Else
intHotPotato = Me.lstHotPotato.Value
End If

If IsNull(Me.lstJunior.Value) Then
intJunior = Null
Else
intJunior = Me.lstJunior.Value
End If

My double quotes module is:

Public Function dbl_quotes(str)

If IsNull(str) Then
dbl_quotes = ""
Else
dbl_quotes = Replace(Replace(str, "'", "''"), """", """""")
End If

End Function

Really frustrated! Thanks so much for all of your help!

 

[newt]

Thanks John. Indeed, my RowSource query did not contain the column. The
RowSource query is generated when another field is populated, but rather than
looking at the RowSource query to find the problem, I was looking at a
QueryBuilder somewhere that did have the column -- and now, for the life of
me, I can't find the QueryBuilder that I was looking at before. Oh well.

Now I have another problem. I keep getting a syntax error that I know has
something to do with a Null value problem. My code is:

ssql = "insert into x_task (project_id, task_subcategory_id,
task_description, supervisor_id, lead_id, " & _
"junior_id, staffing_notes, task_status_id, hot_potato_id,
task_status_notes) " & _
"values (" & Me.lstProject.Value & "," & Me.lstTaskSubType.Value &
",'" & _
dbl_quotes(Me.txtTaskNotes) & "'," & Me.lstSupervisor.Value & "," &
Me.lstLead.Value & "," & intJunior & ",'" & _
dbl_quotes(Me.txtStaffNotes) & "'," & Me.lstTaskStatus.Value & "," &
intHotPotato & ",'" & _
dbl_quotes(Me.txtStatusNotes) & "');"

where I have intJunior and intHotPotato defined as follows:

If IsNull(Me.lstHotPotato.Value) Then
intHotPotato = Null
Else
intHotPotato = Me.lstHotPotato.Value
End If

If IsNull(Me.lstJunior.Value) Then
intJunior = Null
Else
intJunior = Me.lstJunior.Value
End If

My double quotes module is:

Public Function dbl_quotes(str)

If IsNull(str) Then
dbl_quotes = ""
Else
dbl_quotes = Replace(Replace(str, "'", "''"), """", """""")
End If

End Function

Really frustrated! Thanks so much for all of your help!

 

[John W. Vinson]

Now I have another problem. I keep getting a syntax error that I know has
something to do with a Null value problem. My code is:

ssql = "insert into x_task (project_id, task_subcategory_id,
task_description, supervisor_id, lead_id, " & _
"junior_id, staffing_notes, task_status_id, hot_potato_id,
task_status_notes) " & _
"values (" & Me.lstProject.Value & "," & Me.lstTaskSubType.Value &
",'" & _
dbl_quotes(Me.txtTaskNotes) & "'," & Me.lstSupervisor.Value & "," &
Me.lstLead.Value & "," & intJunior & ",'" & _
dbl_quotes(Me.txtStaffNotes) & "'," & Me.lstTaskStatus.Value & "," &
intHotPotato & ",'" & _
dbl_quotes(Me.txtStatusNotes) & "');"

where I have intJunior and intHotPotato defined as follows:

If IsNull(Me.lstHotPotato.Value) Then
intHotPotato = Null
Else
intHotPotato = Me.lstHotPotato.Value
End If

If IsNull(Me.lstJunior.Value) Then
intJunior = Null
Else
intJunior = Me.lstJunior.Value
End If

Well, first off... you can leave off all of the .Value specifiers, since
that's the default property of a Control object.

Are these listboxes Multiselect? If so, they don't HAVE a .Value property and
you'll need to loop through their ItemsSelected property instead.

If you set a breakpoint, what does ssql end up looking like? What are the Dim
statements for intHotPotato and intJunior? If they're integers then they
cannot be set to NULL, you'll need to dim them as Variants to do so; and if
you want to include a null value in a Values() clause then you need to insert
the literal text string Null, without quotes.

Why are you going to all the trouble to use an unbound form and a
single-record append query rather than just using a bound form?

 

[newt]

Thanks very much, John. I really appreciate all of your help. I have to
apologize - I am new to Access and mostly self-taught (meaning by thses
forums!), so I am not familiar with a lot of the terminology. That said, let
me try to answer your questions:

The boxes are not multiselect.

My Dim statement is: Dim ssql As String, intJunior, intHotPotato


Here is what my sql string ends up looking like:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');

I'm not sure if this will answer your question about bound vs. unbound form,
but my database is set up as a single form with 4 areas - the list box
selection in area 1 determines the other fields in area 1 as well as the list
box choices in area 2, the box selection in area 2 determines the other
fields in area 2 as well as the the list box choices in area 3, and the box
selection in area 3 determines the other fields in area 3 as well as the the
list box choices in area 4.

Thanks again.

 

[newt]

Thanks very much, John. I really appreciate all of your help. I have to
apologize - I am new to Access and mostly self-taught (meaning by thses
forums!), so I am not familiar with a lot of the terminology. That said, let
me try to answer your questions:

The boxes are not multiselect.

My Dim statement is: Dim ssql As String, intJunior, intHotPotato


Here is what my sql string ends up looking like:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');

I'm not sure if this will answer your question about bound vs. unbound form,
but my database is set up as a single form with 4 areas - the list box
selection in area 1 determines the other fields in area 1 as well as the list
box choices in area 2, the box selection in area 2 determines the other
fields in area 2 as well as the the list box choices in area 3, and the box
selection in area 3 determines the other fields in area 3 as well as the the
list box choices in area 4.

Thanks again.

 

[John W. Vinson]

Thanks very much, John. I really appreciate all of your help. I have to
apologize - I am new to Access and mostly self-taught (meaning by thses
forums!), so I am not familiar with a lot of the terminology. That said, let
me try to answer your questions:

The boxes are not multiselect.

My Dim statement is: Dim ssql As String, intJunior, intHotPotato

OK, that's going to by default dim them as Variants (so that's not the
problem).

Here is what my sql string ends up looking like:

insert into x_task (project_id, task_subcategory_id, task_description,
supervisor_id, lead_id, junior_id, staffing_notes, task_status_id,
hot_potato_id, task_status_notes) values (2,21,'Review
account',1,5,,'',1,7,'Jane reviewing');

It's probably the ,, for junior_id that's causing the problem. Try using

NZ(intJunior, "NULL")

instead of just intJunior.

I'm not sure if this will answer your question about bound vs. unbound form,
but my database is set up as a single form with 4 areas - the list box
selection in area 1 determines the other fields in area 1 as well as the list
box choices in area 2, the box selection in area 2 determines the other
fields in area 2 as well as the the list box choices in area 3, and the box
selection in area 3 determines the other fields in area 3 as well as the the
list box choices in area 4.

A bound form has a Recordsource property (and can be used to edit data
directly in a table, with no code at all). An Unbound form has no recordsource
and requires code (such as you are writing) to get data from, and write data
to, a table. Unbound forms and code to migrate the data have their place, but
they're a WHOLE LOT more work than a simple bound form, and should be used
only when there's a very good reason to do so.

 

[newt]

Thanks John. I did what you said but still get the same ssql output and the
same error....

 

[newt]

John - I just cleaned up the code and voila, it's working - thanks! That nz
function is good to know!

I hope you can help me with one last thing. I want to populate a date stamp
field in the table only if the txtStatusNotes text box contents are changed
when the record is updated. I am using the following code at the end of my
ssql, which isn't working:

If Me.txtStatusNotes = Me.txtStatusNotes.OldValue Then
ssql = ssql
Else
ssql = ssql & " , date_status_notes_edited = date() "
End If

 

[newt]

John,

I have everything working now, except for one thing. The NZ statements work
well to populate the table with "NULL" but then when I later load the record
into the form, and try to save it without changing that field, I get the same
old error, and my ssql statement shows that there is just a blank inserted,
not "NULL". In order to work around this, I have to select a value in the
list box, and then delete it, before saving it works.

Any ideas?

Thanks. So close....

 

[John W. Vinson]

John,

I have everything working now, except for one thing. The NZ statements work
well to populate the table with "NULL" but then when I later load the record
into the form, and try to save it without changing that field, I get the same
old error, and my ssql statement shows that there is just a blank inserted,
not "NULL". In order to work around this, I have to select a value in the
list box, and then delete it, before saving it works.

Please post your code. My guess is that you have to requery something but I'm
not sure what.

 

[newt]

Here is the code when the "save" button is pressed:

ssql = "update x_task set project_id = " & Me.lstProject & ",
task_subcategory_id = " & Me.lstTaskSubType & _
", task_description = '" & dbl_quotes(Me.txtTaskNotes) & "',
supervisor_id = " & Me.lstSupervisor & _
", lead_id = " & Me.lstLead & ", junior_id = " & Nz(Me.lstJunior,
"Null") & " , staffing_notes = '" & _
dbl_quotes(Me.txtStaffNotes) & "', task_status_id = " &
Me.lstTaskStatus & " , hot_potato_id = " & _
Nz(Me.lstHotPotato, "Null") & " , task_status_notes = '" &
dbl_quotes(Me.txtStatusNotes) & "', date_edited = date() "

'populate date stamp field
ssql = ssql & " , date_status_notes_edited = date() "

ssql = ssql & "where task_id = " & Me.lstTask

 

[John W. Vinson]

Here is the code when the "save" button is pressed:

ssql = "update x_task set project_id = " & Me.lstProject & ",
task_subcategory_id = " & Me.lstTaskSubType & _
", task_description = '" & dbl_quotes(Me.txtTaskNotes) & "',
supervisor_id = " & Me.lstSupervisor & _
", lead_id = " & Me.lstLead & ", junior_id = " & Nz(Me.lstJunior,
"Null") & " , staffing_notes = '" & _
dbl_quotes(Me.txtStaffNotes) & "', task_status_id = " &
Me.lstTaskStatus & " , hot_potato_id = " & _
Nz(Me.lstHotPotato, "Null") & " , task_status_notes = '" &
dbl_quotes(Me.txtStatusNotes) & "', date_edited = date() "

'populate date stamp field
ssql = ssql & " , date_status_notes_edited = date() "

ssql = ssql & "where task_id = " & Me.lstTask

I'm getting fuzzled with all the concatenations. Could you set a breakpoint in
the code and step through it? What does ssql end up containing when you're
done?

And why are you using both literal singlequotes *and* the dbl_quotes function
on txtStaffNotes?

 

[newt]

Here is the code, easier to read:

ssql =
"update x_task set project_id = " & Me.lstProject &
", task_subcategory_id = " & Me.lstTaskSubType &
", task_description = '" & dbl_quotes(Me.txtTaskNotes) &
"', supervisor_id = " & Me.lstSupervisor &
", lead_id = " & Me.lstLead &
", junior_id = " & Nz(Me.lstJunior, "Null") &
", staffing_notes = '" & dbl_quotes(Me.txtStaffNotes) &
"', task_status_id = " & Me.lstTaskStatus &
", hot_potato_id = " & Nz(Me.lstHotPotato, "Null") &
", task_status_notes = '" & dbl_quotes(Me.txtStatusNotes) &
"', date_edited = date() " &
", date_status_notes_edited = date() " &
"where task_id = " & Me.lstTask

After a record is updated a first time with junior_id=Null, if I try to
update the record a second time without touching the empty junior Id list
box, the output is "junior_id = ," instead of "junior_id=Null".

The double quotes code is just what I was taught - I am using it throughout
the form with no problems. The module is:

If IsNull(str) Then
dbl_quotes = ""
Else
dbl_quotes = Replace(Replace(str, "'", "''"), """", """""")
End If

Also, on a couple of occasions now, when I successfully update a record with
an empty (i.e., Null) hot_potato_id field, the record no longer shows up in
the form when it should (i.e., when I click the button that should populate
the form fields with the record -- it disappears, even though it remains in
the table....

By the way, I am using "MsgBox ssql" for a break -- what command can I use
that will output a string that I can copy and paste?

Thanks again, as always, for all of your help!!