Can append query get value from combobox directly?

[Guest]

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me I
need to base my query on a table or another query. I want to base my query on
the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[tina]

I want to base my query on

the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

what do you mean by "not working"? do you get an error message? if so, at
what point - when you save the query? run the query? more details, please.
and can you include the SQL statement in your reply so we can examine it?

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me I
need to base my query on a table or another query. I want to base my query on
the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[Guest]

This worked in my test just now in Access 2003, and if your syntax is the
same, perhaps there is a version-related issue here:

INSERT INTO tblTestReportStandards ( fldStandard )
SELECT [Forms]![Form1]![cboStdAmd] AS Expr1;

I did not account for where you get the correct fldTestReportID for your
query, but you might just test it with the single field for starters.

What verson of Access? I seem to recall an issue like this with either
Access 2000 or XP. As I recall, the solution there was to create a dummy
table with a single field & single record. Add the dummy table to your query,
but do not include any of its fields in the append (it will append the
[Forms]... once for each record in the dummy table, but without any data from
the dummy table).

Someone probably has a better way, so I stand to be corrected...

 

[Guest]

Here is my SQL statement:

INSERT INTO tblTestRprtStds ( fldTestrprtStdRefNum )
SELECT [Forms]![frmTestRprts]![cboStdAmd] AS Expr1;

When I run it I receive the message:

Microsoft Access can’t append all the records in the append query.
… and 1 record(s) due to validation rule violations.
Do you want to run the action query anyway?

Then:

Run-time error ‘2465’:
Microsoft Access can’t find the field ‘Forms’ referred to in your expression.
You may have misspelled the field name or the field may have been renamed or
deleted.

Private Sub Command46_Click()
DoCmd.OpenQuery "qryAppendStd" ‘highlighted yellow in code window
End Sub

Thanks
Seth

I want to base my query on
the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

what do you mean by "not working"? do you get an error message? if so, at
what point - when you save the query? run the query? more details, please.
and can you include the SQL statement in your reply so we can examine it?

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me I
need to base my query on a table or another query. I want to base my query on
the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[Guest]

Should have mentioned that I'm using Access 97

 

[tina]

comments inline.

Here is my SQL statement:

INSERT INTO tblTestRprtStds ( fldTestrprtStdRefNum )
SELECT [Forms]![frmTestRprts]![cboStdAmd] AS Expr1;

well, the syntax looks okay to me.

When I run it I receive the message:

Microsoft Access can't append all the records in the append query.
. and 1 record(s) due to validation rule violations.
Do you want to run the action query anyway?

okay, you have a validation rule in one of the fields in tblTestRprtStds,
and the rule is being violated when Access attempts to append the new
record. review the table to see what that rule is and on which field, so you
can figure out how to satisfy it.

Then:

Run-time error '2465':
Microsoft Access can't find the field 'Forms' referred to in your expression.
You may have misspelled the field name or the field may have been renamed or
deleted.

i'd try to fix the first problem (validation rule violation) first. it's
possible that Access is losing track of the location of the FROM value when
the first error kicks.

if you fix the validation error and still get this next error, go back and
double- and triple- check the names of form and the control in the form for
correct spelling. and make sure the form remains open while the append query
runs. is your combo box in a subform of the open main form, by any chance?

hth

Private Sub Command46_Click()
DoCmd.OpenQuery "qryAppendStd" 'highlighted yellow in code window
End Sub

Thanks
Seth

I want to base my query on
the current value of the combobox. Have tried the following in the

field
of

the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

what do you mean by "not working"? do you get an error message? if so, at
what point - when you save the query? run the query? more details, please.
and can you include the SQL statement in your reply so we can examine it?

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me I
need to base my query on a table or another query. I want to base my

query
on

the current value of the combobox. Have tried the following in the

field
of

the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[tina]

well, i don't know if that changes the validity of my posted comments, or
not. i haven't used A97 in years. i can only suggest that you follow my
suggestions anyway, to see if they'll help.

Should have mentioned that I'm using Access 97

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me I
need to base my query on a table or another query. I want to base my query on
the current value of the combobox. Have tried the following in the field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[Gary Walter]

Hi Seth,

PMFBI

The info you are giving is changing....

If ......

you have a table "tblTestRprtStds" (y/n?)

which contains a number field "fldTestrprtStdRefNum" (y/n?)

and you have a form "frmTestRpts" (y/n?)

which contains an unbound "cboStdAmd" (y/n?)
(whose source is a value list w/ *1* column?)

and a command button "Command46" (y/n?)

and when you click on the command button,
you want to add a new record to "tblTestRprtStds"
where the field "fldTestrprtStdRefNum" gets the
number in "cboStdAmd" (y/n?)

Private Sub Command46_Click()
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim strSQL As String

varNum = Me!cboStdAmt

If IsNumeric(varNum)=True Then
MsgBox "About to add " & varNum & " to 'tblTestRprtStds'"
strSQL = "INSERT INTO tblTestRprtStds ( fldTestrprtStdRefNum ) " _
& "VALUES (" & CLng(varNum) & ");"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox "Successfully added " & varNum & "to 'tblTestRprtStds'"
Else
MsgBox "Your combobox value " & varNum & " was not numeric!"
End If

ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Here is my SQL statement:

INSERT INTO tblTestRprtStds ( fldTestrprtStdRefNum )
SELECT [Forms]![frmTestRprts]![cboStdAmd] AS Expr1;

When I run it I receive the message:

Microsoft Access can't append all the records in the append query.
. and 1 record(s) due to validation rule violations.
Do you want to run the action query anyway?

Then:

Run-time error '2465':
Microsoft Access can't find the field 'Forms' referred to in your
expression.
You may have misspelled the field name or the field may have been renamed
or
deleted.

Private Sub Command46_Click()
DoCmd.OpenQuery "qryAppendStd" 'highlighted yellow in code window
End Sub

Thanks
Seth

I want to base my query on
the current value of the combobox. Have tried the following in the
field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

what do you mean by "not working"? do you get an error message? if so, at
what point - when you save the query? run the query? more details,
please.
and can you include the SQL statement in your reply so we can examine it?

I have an unbound combobox (cboStdAmd) on my main form. It contains a value
that I want to put into a new record in a table.

The destination table is tblTestReportStandards

tblTestReportStandards
fldTestReportID (foreign key from tblTestReports)
fldStandard


tblTestReports
fldTestReportID (pk)
+ other fields


When I try and create an append query to do the above MSAccess tells me
I
need to base my query on a table or another query. I want to base my
query on
the current value of the combobox. Have tried the following in the
field of
the query builder but not working

[Forms]![frmTestRprts]![cboStdAmd]

Thanks for your help.

Seth

 

[Guest]

With the help of your replies I have found my problem (I think!) Which is a
good start, I just have to fix it now .

Some background:

I have two tables

tblTestRprts
fldTestRprtID
+ other fields

tblTestRprtStds
fldTestRprtStdID (required)
fldTestRprtStdRefNum (required)


fsubTestRprtStds is a subform of frmTestRprts.

Source Object: fsubTestRprtStds
Link Child Fields: fldTestRprtStdID
Link Master Fields: fldTestRprtID

On the main form I use unbound cascading combo boxes to select the standard
to enter into tblTestRprtStds. I then try and append the standard to
tblTestRprtStds using

INSERT INTO tblTestRprtStds ( fldTestrprtStdRefNum )
SELECT [Forms]![frmTestRprts]![cboStdAmd] AS Expr1;


But the append fails because I have not specified a required value for
fldTestRprtStdID. If I entered data directly into the sub form this would be
automatic. But I am bypassing the subform and going directly to the table.

The user will not enter data directly into fsubTestRprtStds.
fsubTestRprtStds will just displays data using unbound text boxes with a
DLOOOKUP.

Why am I doing it this way? Because cascading comboboxes do not remember
their value. This way I can always display the required information in the
subform: fldStdRefNum, fldStdIssueDate, and fldStdAmendment. If I used
cascading combo boxes in fsubTestRprtStds all the used would see is
fldStdAmendment which means nothing without the other two fields.


Regards,
Seth