VB script, INSERT INTO

INSERT INTO [sælger-kunde-relation] (Sælger, Kunde) VALUES ('Sælger 1', 'Kunde 1')

status?

Hej Terry

Har ikke lige været ved computeren for nylig.

Ikke helt det svar jeg ønskede. SQL-delen er ikke den ukendte for mig, hvorimod det at forbinde og foretage kald til databasen via VB står mig meget, meget fjernt.

Undskyld mit dårligt formulerede spørgsmål!

//Jesper

- måske jeg kunne kombinere en form og et query og sluppe for det der VB-pjat...

Tester det onsdag... så hører du nærmere!

//Jesper

If you make your tables first and the relationships are in place, then you should be able to make a form using the form wizard. This should make a form  containing a subform.


In the sælger-kunde-relation table when you are in design view choose the Lookup as the data type. This will start a wizard which will help you make the relationship to the related tables. When you are finished you should end up with two combo boxes where you can choose sælger and kunde.


Now make a query where you have the sælger-kunde-relation table and the kunde table. Choose ALL fields from both both tables.


Now make a form using the form wizard. Choose the sælger table AND the query you just made for the data you want to use on the form. Now hopefully you should end up with a form containing sælge and a sub form containig the sælger-kunde-relation and kunde data (from the query).

You may need to alter the field type FROM text to combo





The main form should contain "sælger" and the sub form information from the other two tables (query). Some of the fields are in fact NOT needed, they get automatically filled out when you choose a Kunde.

You also need another form for creating Kunde.

Looking at the problem in detail...

I have this:
---------------------------
kunde_sælger_relation (table)
---------------------------
kundenr        : Text
Sælger        : Number
startdato    : Date/Time
slutdato    : Date/Time

----------------------------------
Form with two text boxes
----------------------------------
txt_kunde_dummy  (Kunder.Kundenr)
txt_salger_dummy (Sælgere.ID)

----------------------------------
These relations
----------------------------------
kunde_sælger_relation.kundenr --> Kunder.Kundenr
kunde_sælger_relation.Sælger  --> Sælgere.ID
---
And i would like to execute these two queries:
----------------------------------------------------------
1.
UPDATE    kunde_sælger_relation SET slutdato = NOW()
WHERE    kunde_sælger_relation.kundenr = txt_kunde_dummy
AND    kunde_sælger_relation.slutdato = ""

2.
INSERT INTO kunde_sælger_relation
VALUES    (txt_kunde_dummy, txt_salger_dummy, NOW()+1, NULL)
----------------------------------------------------------

Is it possible to do this by making the two queries in design-mode and then running them from a VB script?

//Jesper

when you say "VB Script" are you using the SQL in ASP?

no, in Access

//Jesper

If it is in Access then you do not need to use SQL.

You can use this in a form module

docmd.runsql "SQL goes here"

It almost works now, but i have a problem enclosing txt_kunde_dummy in " ... "

INSERT INTO kunde_sælger_relation
VALUES ([txt_kunde_dummy], [txt_salger_dummy], NOW(), NULL);

How do I put "" around [txt_kunde_dummy], so it will insert the value (a string).

can I escape a " in any way?

//Jesper

If you are doing this in code then the best way is to use a string variable for the SQL

Dim sSQL as String


sSQL = "INSERT INTO kunde_sælger_relation VALUES (" & [txt_kunde_dummy] & ", " & [txt_salger_dummy] & " , NOW(), NULL);"

now I just need to get this working:

sSQL = "UPDATE kunde_sælger_relation SET kunde_sælger_relation.slutdato = Now() WHERE (((kunde_sælger_relation.slutdato) = NULL) AND ((kunde_sælger_relation.kundenr)= """ & [Forms]![frm_Admin].[txt_kunde_dummy].[Value] & """));"

it is the part where i want to pick only those posts with slutdato = NULL but NONE of the fields equals NULL...
- the question is, I guess: Wvat is the NULL-value of a date/time field? ... aparently not NULL

//Jesper

the best way to get your SQL working is use the query builder. TYhen when its working there you can copy it to your code.

Try
Is Null