Get form VBA variables into query where clause

[vbaInet]

Referential Integrity is not a bug. It's there to stop you from adding redundant records, i.e. the record must exist in the parent table for it to exist in the child table.

My problem here is just that you're not explaining your problem well. I've asked specifically for you to elaborate by telling me which exact record to look for, which control to look at but your explanations still continue to be vague. There are 3 tables, 5 queries and 1 form in your db.

I need you to tell me which exact record to select on the form, which exact item to look for on the Vendor Data listbox and Customer name respectively, then I hit the button and tell me which exact record ID is different from what is in the Vendor Data listbox. Explain it as if this was a manual you're writing - in bullet points.

 

[dcavaiani]

I'll send this again. Don't even use the form - just go right into the Materials Table and enter a new record. Then, set the R.I from Supplier to Materials on the SupplierNo to VendorNo 1 to many - and see how the error occurs when trying to create a new record!

 

[vbaInet]

You were using lookups in the design properties of the VendorID field in the Materials table that's why RI hindered you from adding redundant records. Avoid using lookups because they mask the real value. The real values you had in the Materials table were 0, 1 and 2 rather than 62, 63 and 64 (which correspond to your Vendors table). The following link explains why you should avoid Lookups at table level:
http://access.mvps.org/access/lookupfields.htm

I've made the necessary changes so see attached.

NB: When you're posting your db in the future, please ensure that you unhide all the relevant tables, queries etc.

 

[dcavaiani]

I won't have access to my PC for a while now.

 

[vbaInet]

You just missed my last post.

 

[dcavaiani]

Still not right. I think I see where the query filter for the form is going to mess things up?

When I open the form now, the vendorid field in no longer highlighted = for the existing records in the Table. The pre-query selects just the Uninvoiced records from Materials so I think I need to re-design this ?

 

[The_Doc_Man]

Just a word of warning about directly executing queries, which you were showing earlier.

There are two big ways to run queries using VBA. One involves the DoCmd.RunSQL option; the other uses DAODatabase.EXECUTE sql-clause. You are limited in what you can do with sql text in the EXECUTE method because of where/how it is executed. The RunSQL method can access all VBA libraries "on the fly." To get the results you wanted with .EXECUTE, you have to separately build the sql in a string variable before you attempt to execute it.

Consider that a side note to warn you of a pitfall if you start experimenting.

 

[vbaInet]

The pre-query selects just the Uninvoiced records from Materials so I think I need to re-design this ?

All I looked at (as you instructed in post #42) was the two tables and after sorting out the tables I was successful in creating a record in the Materials table.

If you wanted me to look any deeper into the issue, I would have mentioned that you need a complete re-design. Your tables need to be properly normalised, lookups removed, subdatasheets removed, queries re-done to include the right fields, form linked to the right field etc. On that note one of us can help with this and I think it would be beneficial if you create a new post in the Tables forum explaining your project.

 

[dcavaiani]

Let's try a real simple example. 2 Tables, Supplier Master Table with a non meaningful key. and a Materials used table which references the Supplier table on a 1 (supplier to many Material records). 1 form used to bring up the Material records already in the Table, allowing for an update if necessary, with the Supplier code in a dropdown And highlighted to show the current supplier of the displayed or scrolled to Material record. I realize that the Materials should probably be a Master / Detail design but not at this time.

 

[dcavaiani]

Looks like all work just fine when I simply use the wizards. Looks like my VBA code is the culprit - not very good!!http://images.access-programmers.co.uk/forums/images/smilies/banginghead.gif

 

[dcavaiani]

To all who advised me on here, and especially to vbaInet, a big THANK YOU!

 

[vbaInet]

Sounds like you've resolved the problem dcaviani. What did you end up doing? Did you also manage to re-structure your tables?

 

[dcavaiani]

Basically just starting from scratch - using the wizards to begin with. That seems to fix the dropdown issues thus far. Now, if I don't screw things up again with my VBA code ;-)

 

[vbaInet]

Ok, and I hope that included your tables too.

Now, if I don't screw things up again with my VBA code ;-)

Hopefully