Re: Match fields with VBA

Tech-Archive recommends: Fix windows errors by optimizing your registry

I went a little different route, using a filter on recordset. However, I
can't seem to pass the variable into the mix.

This works.

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = 2")

But then I replace the 2 with a variable and it bombs

Dim var As Integer

var = 2

Set rsttasks = db.OpenRecordset("SELECT * " & _
"FROM [MSP_Tasks] WHERE [TASK_UID] = var")

I've also tried it with var defined as a string.

Thoughts? Thank you



"Tim Ferguson" wrote:

> "=?Utf-8?B?U2Vhbg==?=" <Sean@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> news:ED861955-7CD3-41EF-A252-30A61B02E2F7@xxxxxxxxxxxxx:
>
> > strname = Select TASK_NAME " & _
> > "from MSP_TASKS " & _
> > "where MSP_TASKS!TASK_ID = MSP_LINKS!LINK_PRED_ID"
>
>
> A couple of problems:
>
> a) SQL does not have a bang! operator. You access columns of a table
> using a dot operator only: Msp_Tasks.TaskID
>
> b) Msp_Links.Link_Pred_ID is not a single value -- it's a whole column of
> values. Therefore this particular constraint is not meaningful even if it
> were grammatic.
>
> You can indeed create SQL commands in VBA -- in fact it's normal
> procedure -- but there are two things to do first:
>
> (a) make sure you know what you are trying to achieve ("pulling a value
> from a table" does not make sense without _much_ more information), and
>
> (b) get the SQL debugged first. The easiest way to do this is to use the
> query designer in Access itself: then either switch to SQL view to copy
> the code into your vba editor; or just save the thing as a querydef and
> base your recordset on that.
>
> Hope that helps
>
>
>
> Tim F
>
>
.

Relevant Pages

  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... holds the current text in the control, ... Sometimes the unbound controls do have a value (such as when they are set ... I need this value to assign a unique ID to the entire recordset being ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)