Cleaner Code with DoCmd.RunSQL Insert into

[mduplantis]

DoCmd.RunSQL "insert into tbl_radio_inventory (Serial_Number,RID,Company,Display,Date_Issued)val ues([txt_Serial_Number],[txt_RID],[txt_Company],[txt_Display],[txt_date_issued])"


I am using the above code as an example of connecting an unbound form of the front end to populate my tables in the backside. Although this is fine for a simple form, I have a few other forms that have about 20 text fields, combo and list boxes.

Is there anyway for me to clean this up? Maybe another method or just assigning some variables to shorten the statement.

Thanks,

Mike

[CJ_London]

can you confirm the code actually works as is. Also, can you explain why you are using an unbound form? No reason not to but the reason for doing so can have an impact on the solution.

[mduplantis]

Yes, the code does work. Below is the full procedure.

I am using an unbound form, simply because the example I am working with uses and unbound from with the above code. I have thought of other ways, but so far, this seems to make sense to me. Open to any suggestions.

Thanks

Private Sub Command12_Click()If IsNull([txt_Serial_Number]) Then
MsgBox "Serial Number Cannot be Blank"
ElseIf IsNull([txt_RID]) Then
MsgBox "Radio ID Cannot be Blank"
ElseIf IsNull([txt_Company]) Then
MsgBox "Company Cannot be Blank"
ElseIf IsNull([txt_Display]) Then
MsgBox "Display Cannot be Blank"
ElseIf IsNull([txt_date_issued]) Then
MsgBox "Date Issued Cannot be Blank"
Else
DoCmd.RunSQL "insert into tbl_radio_inventory (Serial_Number,RID,Company,Display,Date_Issued)values([txt_Serial_Number],[txt_RID],[txt_Company],[txt_Display],[txt_date_issued])"
[me.txt_Serial_Number] = Null
[me.txt_RID] = Null
[me.txt_Company] = Null
[me.txt_Display] = Null
[me.txt_date_issued] = Null


End If
End Sub

[CJ_London]

Is there anyway for me to clean this up? .... Open to any suggestions.

just use a bound form, then no insert code required.

With regards your other code checking for missing required data, you would put that in the form before update event and cancel if fails

with regards simplifying your other code, and usable in any form, set the tag property of each control that is required to 'Required' then create the following function in a standard module (not form module)

Public Function NotCompleted(frm as Form) as boolean
Dim ctrl as control
for each control in frm.controls
    if ctrl.tag="required" then
        if isnull(ctrl) then
            msgbox ctrl.name & " required" '(or use ctrl.controlsource)
            NotCompleted=True
            exit sub
        end if
    end if
next 'ctrl
NotCompleted=False
End Function

Then call this function from your form beforeupdate event like this

Cancel= Allcompleted(me)

[mduplantis]

This is a very helpful function. I will give it a try. Thanks!

[mduplantis]

I am not sure I understanding calling the function

Cancel= Allcompleted(me)

The function's name is NotCompleted.

Thanks

[CJ_London]

sorry, I changed the function name so it read better

Cancel= Notcompleted(me)

[mduplantis]

Okay, I should have assumed that. I'll test it again this morning. I had to read up on tags a little bit.

thank you

[Missinglinq]

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything, even the most mundane tasks!

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

[CJ_London]

@Linq: puzzled by what you mean by this

Your data security is far, far better than anything you can do in Access

[mduplantis]

Yes, I have read that from many people, but I guess I am not familiar with creating a bound form and then only allowing certain access. I am more than willing to look at a bound form if you could kindly directly me to a few articles/videos on how to deploy my database on a network server.

Thank you for the comments.

[rpeare]

Example I did for someone else using unbound controls.

There are some basic instructions on using the global functions on the form. The form uses the TAG function extensively to work.

UnboundDataEntryExample.zip

[mduplantis]

I took a quick look at it and it makes sense to me. I'll spend some more time looking at it in depth tonight. Thanks for the sample!

[mduplantis]

I took a much closer look at the example and I think I understand most of it. One follow up questions would be to asked about updating multiple tables?

Also, MissingLinq, I am still interested in your thoughts about bound forms. I am assuming you are referring to creating a split database with a copy of Access on each remote computer? All of our computers (about 60) have Access loaded, but I had a few concerns about deploying that many copies of the database. That is one reason I was looking at creating a front end in a network folder, so there was only one form to manage. Another concern was having a linked table that would tie up other users.

For this project, I will only be collecting data in a form, but it does go into multiple tables.

Thanks for all the comments. Reading up on all suggestions.

[Missinglinq]

Yes, I am talking about a split database with each user having a copy of the front end on their PC. Having multiple users sharing a single Front End, on a network drive, is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior!

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past nine years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing these kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

Their are a number of hacks out there for easily handling the distribution of front end updates…here are a couple of them:

http://www.utteraccess.com/forum/Aut...E-t135969.html

http://www.rogersaccesslibrary.com/f..._topic409.html

Linq ;0)>