Checking in empty table

[firework]

Hi,

I want to insert some records into a table. (The records are from one table (TProducts) and depended on user input, the records will be copied to another table (TProject) for user to make changes. Hence in the Table TProject, the records would be vary).

At present, I used a query to do it , during the docmd.openquery, I did a DLookUp to see whether there was record already inserted. If there was then I don't need to do the insertion of record otherwise I append records to TProject.

I received runtime error 94. how can I check for this error in the code (don't know how to) some basic syntac would be good.

Or anyone has any suggestion as to how to achieve the above, insert records into another table base on user selection.

Many thanks

 

[Banista]

what does the user input look like?

In general, you can manipulate tables this way:

'Header:

    Dim DataDB As Database
    Dim RS_Article_Data, RS_results, RS_Control As Recordset

set datadb = currentdb
Set RS_results = DataDB.OpenRecordset("ABC_Results", dbOpenDynaset)
Set RS_Input_Table = DataDB.OpenRecordset("Input_Table", dbOpenTable)
Set RS_Article_Data = DataDB.OpenRecordset(Selected_query, dbOpenDynaset)

You basically create "recordset" objects based on tables or queries in your database. These recordsets provide access to search functions and table/query content, as well as methods to update said tables.

selected_query could be a SQL string, so you can open queries or tables like this.

Basic addition of a record to a table:

RS_results.AddNew
RS_results![your_field]= RS_input_table![source_field]
RS_results.Update

This code adds a new record to the table referred to by RS_Results ("ABC_Results"), and fills the field [your_field] with the value in the currently active field in RS_Input_Table (by default the first line).

Loop to parse an entire table:

Do Until RS_Control.EOF
    RS_results.AddNew
    RS_results![Klassifizierung] = RS_Control![Klassifizierung]
    RS_results![UmsatzProzent] = 0
    RS_results![KlassifizierungProzent] = RS_Control![Prozent]
    RS_results.Update
    RS_Control.MoveNext
Loop

basic search (very fast, but needs a proper primary key and the dbopentable type):

   RS_Results.Seek "=", Search_string
   
   If RS_Artikel.NoMatch Then
      Exit Function
   Else
      'your pointer is now at the record that has been found
   End If

That code jumps to the record in RS_Results where the primary key field matches Search_string. If no such entry is found, RS_Results.NoMatch will be set to True.

This was only a little crash course on how to manipulate tables in VBA without SQL, but reading up on these commands in the help files should get you where you want fairly quickly. Or just come back and ask more questions with more details on what you want to achieve

Regards
Bani

 

[firework]

Hi there, Thank you for your advice. I find it abit difficult to follow but as you said it was a very crash notes.

Would you mind to show me how to do it in DLookup. I found that if the file is empty or don't have any record for a particular project, I always get the Runtime error 94 - Invalid use of Null. But once I have record for a particaular project, the lookup does work. I have tried to use a number field as well as a text field as my lookup key but both return INVALID USE OF NULL.

Here are my codes:

Dim xtemp As String or double
xtemp = Null or 0
xtemp = DLookup("[Item]", "TProjectitems", "[ProjectID] = '" & Forms!FProject!ProjectID & "'") - Item is a text field
xtemp = DLookup("[TProjectItemID]", "TProjectitems", "[ProjectID] = '" & Forms!FProject!ProjectID & "'") TProjectItemID is a numeric field
If IsNull(xtemp) or = 0 Then
DoCmd.OpenQuery "QTemplate"
End If

Can you help with the dlookup please.

Now after so many tried, when ever I tried to run the code, I got another weire error Runtime error '2001' - You Canceled the previous operation.

HELPPPPPPPPPPPPPPPPPPPP

 

[WayneRyan]

Firework,

Use the Search Facility here and look for the DCount function.

That's what you need.

Wayne

 

[firework]

Hi there,

Please excuse me being naive. I don't understand how could a DCount help. I could do a DCount before append record to the table but it doesn't give me the info whether any record existed for a specific project.

 

[pbaldy]

Wayne appears to be offline. I'd probably use DCount too. A count = 0 means there are no matching records. Your error stems from the fact that a variable type of string or double can not handle the null value that the DLookup will return if it doesn't find a match. You can change that to Variant if you want to use your current method.

 

[WayneRyan]

Firework,

As Paul said, the DCount won't return a Null value if the record
exists (it'll return 0), much easier to deal with.

If DCount("[Item]", "TProjectitems", "[ProjectID] = '" & Forms!FProject!ProjectID & "'") > 0 Then
   ' MsgBox or whatever (record exists)
End If

Paul, no holiday for you either?

hth,
Wayne

 

[firework]

Hi there,

Thank you for replying. I understand what you are saying. I may have used the wrong wordings for the title.

last week the error only occurred when the table is totally empty. If there was record, I don't get any error when appending the records to the table for anothe project. However today, I just hit wall everywhere. Beside the empty table, if I have new project (that is the reason which I do a look up to make sure that no other users had already done the task) using the project ID. If no record was found then I do the append otherwise I skip. I got error which was also very weired error.

In this case, any other method which I would check if any record existed for a particular record please.

Many thanks

 

[firework]

Hi Guys,

Thank you so much. Now it make senses. Apology being so 'stuborn'. Getting too late.

Have a nice evening.

 

[pbaldy]

Paul, no holiday for you either?

No rest for the weary, Wayne.

 

[WayneRyan]

Paul,

If you were playing ... I'd have caddied for you today.

My friend got his first eagle (145 yd. 8-iron for a deuce).
Playing by himself.

Oh well,
Wayne

 

[pbaldy]

Oh, that really bites. The only thing worse would be a hole in one with no witness. No golf for me. It was snowing most of the morning, courses are covered and it's too freaking cold anyway. And don't tell me how nice it is down there. I don't want to hear it!

 

[firework]

Hi guys,

Thank you so much for your help. The checking is resolved but I will investigate Binsta's suggestion for future reference.

Have a good evening

 

[Banista]

If all you needed was a protection against Null exceptions, it'd probably be overkill, but I find the RecordSet way a very convenient framework when tightly integrating table manipulation with actual code.

Good look in your endeavours