Creating records in VBA 1

[ragnarok75]

I need the right way to go about the following scenario:

I need to update tableD with values from tableA, tableB, and multiple values from tableC.

Tables\Fields Record1 Record2 Record3
TableA
FldNumA 1

TableB
FldNumB 5

TableC
XYNum 100 200 B67

TableD
FldNumA 1 1 1
FldNumB 5 5 5
XYNum 100 200 B67

There is one TableA.FldNumA for TableB.FldNumB but there are multiple TableC.XYNum. I need to create records in TableD for all the TableC.XYNum that relate to TableA.FldNumA and TableB.FldNumB.

Right now I am trying to accomplish this using an insert statement that is launched by clicking a button on a form that has TableA and TableB loaded. The multiple values from TableC are loaded via a query, but I do not know how to cycle through the records in the query with the insert statement. Also, since XYNum can be numbers and letters, or just numbers I am having issue with the insert statement handling both data types.

Is there a better way to do this? Any ideas are appreciated!!

 

[CautionMP]

ragnarok75,
Can you create a query (in the QBE pane) that has all the records you want to insert into tableD?
If so you could make it an append query and fire that from you button, letting Access handle the dirty work.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

There is one TableA.FldNumA for TableB.FldNumB but there are multiple TableC.XYNum
What are the relationships between TableA, TableB and TableC ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragnarok75]

Caution - The append query will work if I can specify a where clause. I have written the query and tested it, but I need to be able to pull a value from a text box on the form and put it in the where clause of the query to run it. How do I do this in code?

 

[ragnarok75]

PHV - The tables are related in the following way:

TableB FldNumB is a foreign key in TableA. TableA FldNumA is a foreign key in TableD. TableC XYNum is a foreign key in TableD. FldNumB in TableD above is a mistake - This field will come from TableC.

 

[ragnarok75]

Caution -

I have the append query working in code, the only issue I have now is that it prompts the user with the standard access message stating how many records it is about to append and asking to click yes or no - Is there a way to suppress this prompt?

 

[ragnarok75]

Caution -

OK - sorry for the continued posts, I have figured out how to append the access warning message. How do I deal with errors? Right now, any access message is suppressed, if there is an error, I will never know.

 

[CautionMP]

ragnarok75,
First I would [tt]SetWarnings = False[/tt] just before running the Append query, then [tt]SetWarnings = True[/tt] right after. This way the you only supress the message from the Append query.

Second, Error handlers are a good thing. To clarify your question, are you looking for errors from the Append query, or else where in your code?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ragnarok75]

I was looking for error handlers in the append query. If the record is not added, I want some sort of warning to let me know.

 

[CautionMP]

ragnarok75,
1) You want a message to let you the Developer know there was an error appending?
- or -
2) You want a message to let you the User know there was an error?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ragnarok75]

Both - If the append does not happen, then the user cannot move to the next step in the DB.

 

[CautionMP]

ragnarok75,
A slightly different approach to catch Append errors

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
MyDB.Execute "[i][b]YourAppendQueryName or SQL here[/i][/b]", dbFailOnError
If MyDB.RecordsAffected = 0 then
  err.Raise 8000, "Append Records","There were no records to append"
Rnd If
'...
'Your code here
'...
Set MyDB = Nothing

If there are any append errors this will raise Error 3022 which you can respond to, I also threw in Error 8000 in case there were no records to append.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)