Basic query problem

[mattpearcey]

I need to set up a query that throws the result of a field in a form (that comes from my main table - country) into a table. Basically. I have a country info table, that relates to the countries of my records. The form takes the country from the main table. But then i cannot save any data i enter on these countries as i cannot get it to put the data into this new table.

So, i need to make a query that puts the country in the country field in the CountryInfo table. Then i can set this to no duplicates. Then i need to set the query to look for a match on the country field everytime the user goes into this form, so that it goes to the already entered record, and does not try and create a new one.

I need help with these queries, as i am not sure what type of queries these are, and what tables to base them on.

details

BankDetails table and form - contains 'Country' field.
CountryInfo table and form - contains 'Country' field.

CountryInfo Form takes country from BankDetails table (filtered from what is on view on the main form)

=[Forms]![Bank Details].[Country]

but then this value is not entered into the CountryInfo table. So - i need to make the query to do this for me?!
Thank you for your help already.

Matt Pearcey

 

[camelman]

Hi there

I very quickly read your question. Time is short so unfortunately so is my answer. If this answer doesn't sort out your problems then I will give you a more in depth answer later on.

It sounds like you need to insert records into a table.
Here is the code to do it.

Dim dbs as database
set dbs = currentdb

dbs.Execute ("INSERT INTO [Table Name] ([field1], [field2], [field3]) VALUES (" & [amount] & ",'" & [name) & "')&quot

I realise that the brackets and inverted commas make it look very confusing but it works as follows.
(" & [amount] & "
,
'" & [name) & "'
)&quot


name is a string therefore it needs the small inverted commas. Amount is a double so it doesn't need them.
I have broken it up into seperate lines in an attempt to indicate how it works.

Good luck.

 

[mattpearcey]

Sorry for sounding stupid, but i need a little clarification as to your instructions.

where do i put the code? just by opening up the code module - or do i build within the Load option of the form? this is where i fall over with lots of code entering.

Anyhow, do you think this would work?

dbs.Execute ("INSERT INTO [CountryInfo] ([Country]) VALUES (" & [amount - not sure what to put here?] & ",'" & [Country] & "')&quot

Confused?!


Thank you for your help already.

Matt Pearcey